Menu
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?
361
Click the cell where you want to enter a reference to another cell. Type an equals (=) sign in the cell. Click the cell in the same worksheet you want to make a reference to, and the cell name is automatically entered after the equal sign. Press Enter to create the cell reference.
Type = followed by the sheet name, an exclamation point, and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'! B4 .
To reference a cell or a cell range in another worksheet in the same workbook, simply put the “worksheet name” followed by an exclamation mark “!”, then the cell reference. In this example, I have my 2020 sales data in another sheet in the same workbook.
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.
148
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
