In Excel, How can I set up the indirect function to use its own cell address

0
votes

I have 10 sheets and on the 11th sheet is my working sheet. In every cell I have an index match formula to output values between 1-10 to reference one of the original 10 sheets. I want to use the indirect function to fill sheet 11 with values from sheets 1 through 10.

For example the indexmatch formula outputs 3 in cell A1 so I want sheet 3 A1 , A2= sheet 7 A2, C3= sheet 1 C3 etc. How can I set up the indirect function to use its own cell address?

1
excelexcel-formula
Do you mean ADDRESS(ROW(),COLUMN())? Though there may be an easier way. – BigBen

1 Answers

0
votes

Method 1

  1. Use the index match + concatenate to build "SheetX!$A$1" string. hint: use concatenate() or &
  2. get the current cell row number - 1 > row()-1 & current cell column number - 1 > column()-1
  3. Feed [1] & [2] into offset() formula.

Method 2

  1. Use BigBen ADDRESS(ROW(),COLUMN()) formula
  2. Use the index match + concatenate to build "SheetX!<cell_address_from_[1]>" string.
  3. feed it to indirect() formula

Both should do. ( :