Refer to a cell in another worksheet by referencing the current worksheet's name?
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?
Answer
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.