Using INDIRECT to reference another sheet in a spreadsheet.

English
#1

I have a budget spreadsheet where:

  1. I have a summary sheet called EXPENSES with cost totals organized by month, i.e. JAN, FEB, MAR etc. So I have the whole year view in one sheet.

  2. The detailed expenses are organized in separate sheets by month, i.e. JAN, FEB, MAR etc. The costs in these sheets are organized in groups (i.e. Groceries, Travel [w trows for tickets, hotels, flight tickets etc.] and each group has several rows of items that sum up to a total for the group. The groups / ranges have been given names.

  3. The detailed expenses in these ‘pick-up’ / data input sheets all have the same structure and the same named groups with the same name. So basically the FEB sheet is a one-to-one copy of the JAN sheet and so on. The only difference is the sheet name in the tab.

  4. In the summary or target sheet with the INDEX formula (EXPENSES) I want to be able to search the named groups to pick up not only the total cost for the group, but also search for itemised cost within a group.

I am using an INDEX MATCH formula where it is told to go to the JAN pick-up / data input sheet (the INDIRECT formula) to pick up the cost data. So I want the formula in the target sheet cell (EXPENSES) to go to Row 5 of the column to see which month it is and then go to the the pick-up / data input sheet for that same month to get the data.

So if the formula is in the FEB column (as stated in cell E5), the INDIRECT formula should create a cell reference to go to the pick-up / data input sheet named ‘FEB’ to pick-up the cost from the specified group or group item

In Excel this formula does just that:

INDEX(INDIRECT(""&E$5&"!GroceriesGroupCost"),MATCH($EXPENSES.$D$57,INDIRECT(""&E$5&"!GroceriesGroup"),0))

where:

  • $E$5s is the cell in row 5 in the formula sheet (EXPENSES) where INDIRECT picks up the name of the month, i.e. FEB.

  • the INDIRECT formula should then use the name/text in cell E5 (FEB) to create the cell reference to the data inut sheet ‘FEB’. So basically create a referece to '$FEB.D63`

So it should take me to the FEB sheet to get the data from the named group GoceriesGroupCost, which is part of the group called GroceriesGroup.

I would like to use the dynamic reference capability in INDIRECT so that I do not have to enter the formula for each and every cell and repeat the process if subsequent changes are made.

Can anyone help me with the INDIRECT formula syntax.

#2

@Haagen, Edit your question, and add empty lines to create new paragraphs. Use the 101010 button to mark formulas.

#3

Hello @Haagen,

Why is the INDIRECT parameter written as CellAddress!SheetName ? Should not it be the opposite, like SheetName!CelleAddress ?

Kind regards, Michel

#4

Hi Michel. Thanks. Let me try to explain.

I am attempting to use the cell address ($E$5 that contains the text ‘FEB’) as a text string in the INDIRECT formula to use as a reference to the pick up cell in another sheet.

So if the INDEX formula is in column ‘E’ cell E55 and this column contains data for the month of FEB, I am attempting to get the INDIRECT formula to use ‘FEB’ text in cell E5 to create a text reference to the separate sheet named FEB.

So the aim is to use the ‘FEB’ text reference from cell E5 in INDIRECT to identify the FEB pick up sheet and stitch the text string (“FEB”) with the name of the range in the FEB sheet to get to the correct cell in Sheet FEB.

#5

I’m mixing . and ! as separators between worksheet name and cell address/named range.

If you only have 12 months, you could use an approach like

=INDEX(CHOOSE(MonthNumber,Jan!GroceriesGroupCost,Feb!GroceriesGroupCost,
Mar!GroceriesGroupCost,Apr!GroceriesGroupCost,May!GroceriesGroupCost,Jun!GroceriesGroupCost,
Jul!GroceriesGroupCost,Aug!GroceriesGroupCost,Sep!GroceriesGroupCost,Oct!GroceriesGroupCost,
Nov!GroceriesGroupCost,Dec!GroceriesGroupCost),MATCH($EXPENSES.$D$57,CHOOSE(MonthNumber,
Jan!GroceriesGroup,Feb!GroceriesGroup,Mar!GroceriesGroup,Apr!GroceriesGroup,
May!GroceriesGroup,Jun!GroceriesGroup,Jul!GroceriesGroup,Aug!GroceriesGroup,
Sep!GroceriesGroup,Oct!GroceriesGroup,Nov!GroceriesGroup,Dec!GroceriesGroup),0))

This is long and awkward, but you could define names like

MGroceriesGroupCost referring to

=CHOOSE(MonthNumber,Jan!GroceriesGroupCost,Feb!GroceriesGroupCost,
Mar!GroceriesGroupCost,Apr!GroceriesGroupCost,May!GroceriesGroupCost,
Jun!GroceriesGroupCost,Jul!GroceriesGroupCost,Aug!GroceriesGroupCost,
Sep!GroceriesGroupCost,Oct!GroceriesGroupCost,Nov!GroceriesGroupCost,
Dec!GroceriesGroupCost)

MGroceriesGroup referring to

=CHOOSE(MonthNumber,Jan!GroceriesGroup,Feb!GroceriesGroup,Mar!GroceriesGroup,
Apr!GroceriesGroup,May!GroceriesGroup,Jun!GroceriesGroup,Jul!GroceriesGroup,
Aug!GroceriesGroup,Sep!GroceriesGroup,Oct!GroceriesGroup,Nov!GroceriesGroup,
Dec!GroceriesGroup)

Then you could shorten the formula to

=INDEX(MGroceriesGroupCost,MATCH($EXPENSES.$D$57,MGroceriesGroup,0))
#6

Replace the Excel sheet name separator ! exclamation mark with the Calc sheet name separator . dot in the indirect reference. Or under Tools → Options → Calc → Formula switch Formula Options Formula syntax from Calc A1 to Excel A1, but that also affects all reference input and display in formula expressions.

#7

Hi. Thanks. I tried it out and I get the REF# result. Then I substituted the rangename (GroceriesGroupCost) with the normal cell ref, i.e. “.d63” and now the formula works.

I can copy it to the next column and instead of the cost for, say, JAN it picks up the cost for FEB.

So, so far so good. It seems to be an issue with identifying the rangename.

When I copied the JAN sheet (with the rangenames defined) and renamed it FEB, the rangename scope changed from Document (Global) to JAN and FEB etc.

But the name box - top left - does not show the name but the normal cell reference, i.e. C34.

So could that be the reason that the formula does not understand what the name GroceriesGroupCost refers to?

And if, so how can I make the name appear in the name box?

#8

get the REF# result. Then I substituted the rangename (GroceriesGroupCost) with the normal cell ref, i.e. “.d63” and now the formula works.

INDIRECT() does not support sheet-local scoped names (yet). It supports sheet-local named ranges without the sheet prefix on the same sheet, or global names. tdf#100818

But the name box - top left - does not show the name but the normal cell reference, i.e. C34.

That’s tdf#67007 implemented for 7.0.3

#9

Thanks. I look forward to 7.0.3

#10

The answer is given in version 7.1 where INDIRECT can now use sheet local names.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.