Problem with Indirect Formula

**jimstrongy** · 03-21-2012, 12:45 PM

I have a formula
=sum(indirect($q$1&"!P7"))

When I pull the formula down to other cells P7 doesn't change.
I need it to change to reference other cells when I pull it down.

Thanks,

**DBY** · 03-21-2012, 12:59 PM

Hello
Try the following amendment to you formula.

=SUM(INDIRECT($Q$1&"!P"&ROW(A7)))

Note the sum is not needed if you're just returning the cell value which is what this does.

Hope this helps.

**Pepe Le Mokko** · 03-21-2012, 01:38 PM

Or simply change the quotes

=sum(indirect("$Q$1!"&P7))

**parihiral** · 03-26-2012, 05:53 PM

Hi I used your suggestion, because I want it to do the same thing where the P7 cell woudl change to P8 and P9 and so on. So i need to go down the column. However when i use your formula it returns a 0

=INDIRECT("'"&TEXT(A38,"mmm yy")&"'"!D"&COLUMN(C7)

**Pete_UK** · 03-26-2012, 07:02 PM

Well if you are going down a column it is the row number that changes, so you need ROW rather than COLUMN in your formula.

Hope this helps.

Pete