How to change cell reference when dragging an INDIRECT formula?

2
votes

I am using the INDIRECT function to reference a specific sheet but when I drag across the row, I want the cell reference to change, NOT the sheet reference. The problem I am having is that my sheet reference for the INDIRECT formula is a column of cells on the same sheet.

This is the kind of formula I want to achieve but at the moment I am having to manually change the cell reference going across the columns. How can I structure the formula to do this by dragging across?

    ColA     ColB                     ColC
   (Sheet)

A3: 50113    =INDIRECT($A3&"!E21")    =INDIRECT($A3&"!F21")
A4: 50900    =INDIRECT($A4&"!E21")    =INDIRECT($A4&"!F21")
A5: 50901    =INDIRECT($A5&"!E21")    =INDIRECT($A5&"!G21")
1
excelexcel-indirect

1 Answers

2
votes

Change your formula in B3 to:

=INDIRECT($A3&"!"&CELL("address",E$21))

and copy across and down.

The point being that the part:

=CELL("address",E$21)

resolves to a reference to the cell:

E$21

and, with correct referencing (here relative column, absolute row), will update accordingly as that formula is copied to further cells.

Regards