drag-down of indirect function does not change cell refereence between speech marks

[nils7]

Hi

I have a financial modelling worksheet with the Baseline figures in Sheet1, then various overlays in the next 10 Sheets. On my output sheet I want to calculate in cell A4 the sum of A$ on Sheet1 plus any of the A4's from the overlay sheets that I select in a drop down on the output sheet. In my current setup I select a maximum of two additional sheets.

On the output sheet i have the name of the sheet that I want to include in a drop-down menu in A1, B1 and C1. Values are: Sheet1!, Sheet2!, Sheet3! etc...

In A4 I then want to display the sum of the A4s from the sheets whose names I have selected in A1, B1 and C1. This is the formula I am using:

Please Login or Register  to view this content.

For A4 this gives the correct result. If I want to drag down the formula to apply to other cells on the output sheet, the value "A4" does not change, because it is in between speech marks. I could do this manually, but it would involve manual input in hundreds of cells.

Do you have any ideas how to work around this?

Thanks a lot,

Nils

[Ace_XL]

One way...

In A4 use..
=SUM(INDIRECT($A$1&ADDRESS(ROW(),COLUMN())),INDIRECT($B$1&ADDRESS(ROW(),COLUMN())),INDIRECT($C$1&ADDRESS(ROW(),COLUMN())))

[benishiryo]

hi Nils, welcome to the forum. something like this?
=SUM(INDIRECT($A$1&"A"&ROW(A4)),INDIRECT($B$1&"A"&ROW(A4)),INDIRECT($C$1&"A"&ROW(A4)))

[nils7]

Thank a lot for the fast responses!

Benshiryo, your solution works a treat when dragging down.I tried to do this using the row() function, but had not suceeded. Thanks for having showing me how to properly combine that with my indirect function. I guess I could amend your solution to also work when dragging across?

Ace_XL, your solution does exactly what I need! Thanks a million! I had not used the Address() function before and this will save me literally several hours of work. Cheers!



Nils