Refer to a cell in another worksheet by referencing the current worksheet's name?

24
votes

I plan to have a workbook with 24 sheets. The sheet names will be: Jan, Jan item, Feb, Feb item, etc.

Basically it's for budgeting, with the month named sheet having summary info, and the ones with "item" in the name having more detailed info.

Now, if I want the "Jan" spreadsheet to have a cell referring to cell J3 in the "Jan item" spreadsheet, I can do the following:

='Jan item'!J3

However, instead of having to re-enter the formulas like this for each summary sheet (Jan, Feb, etc), I would like to have something like:

=(reference-to-this-sheet's-name + " item")!J3

That way, I can copy the formula from Jan to Feb, and it will automatically look at the correct Xxx item sheet without me having to type in Feb item explicitly.

The reason I ask this is because, for each summary sheet, there will be dozens of such references to a cell in the corresponding itemized sheet.

Is what I'm asking doable?

3
excelexcel-formulaworksheet-function
Is VBA code accepted??? Or you need to be only formulas? – Daniel Möller
Just copy the formula from January worksheet, and then do a Find/Replace all, within the February worksheet, replacing "Jan" with "Feb", etc. Otherwise, you'll need to use some VBA. – David Zemens
Daniel - I was planning on only formulas - I have no familiarity whatsoever with VBA. Still, if that's the only solution, I'd like to see it. Worst case, I'll go with David's solution - which seems obvious now that I've read it . . . embarrassingly, it's a solution that was one of those "I can't see it, it's at eye-level!" moments. – King_V

3 Answers

32
votes

Still using indirect. Say your A1 cell is your variable that will contain the name of the referenced sheet (Jan). If you go by:

=INDIRECT(CONCATENATE("'",A1," Item'", "!J3"))

Then you will have the 'Jan Item'!J3 value.

12
votes

Unless you want to go the VBA route to work out the Tab name, the Excel formula is fairly ugly based upon Mid functions, etc. But both these methods can be found here if you want to go that way.

Rather, the way I would do it is:

1) Make one cell on your sheet named, for example, Reference_Sheet and put in that cell the value "Jan Item" for example.

2) Now, use the Indirect function like:

=INDIRECT(Reference_Sheet&"!J3")

3) Now, for each month's sheet, you just have to change that one Reference_Sheet cell.

Hope this gives you what you're looking for!

0
votes

Here is how I made monthly page in similar manner as Fernando:

  1. I wrote manually on each page number of the month and named that place as ThisMonth. Note that you can do this only before you make copies of the sheet. After copying Excel doesn't allow you to use same name, but with sheet copy it does it still. This solution works also without naming.
  2. I added number of weeks in the month to location C12. Naming is fine also.

  3. I made five weeks on every page and on fifth week I made function 

      =IF(C12=5,DATE(YEAR(B48),MONTH(B48),DAY(B48)+7),"")

    that empties fifth week if this month has only four weeks. C12 holds the number of weeks.

  4. ...
  5. I created annual Excel, so I had 12 sheets in it: one for each month. In this example name of the sheet is "Month". Note that this solutions works also with the ODS file standard except you need to change all spaces as "_" characters.
  6. I renamed first "Month" sheet as "Month (1)" so it follows the same naming principle. You could also name it as "Month1" if you wish, but "January" would require a bit more work.

  7. Insert following function on the first day field starting sheet #2:

     =INDIRECT(CONCATENATE("'Month (",ThisMonth-1,")'!B15"))+INDIRECT(CONCATENATE("'Month (",ThisMonth-1,")'!C12"))*7

    So in another word, if you fill four or five weeks on the previous sheet, this calculates date correctly and continues from correct date.