INDIRECT AND changing reference

[w424637]

Hi
I need an Indirect formula to look up data from a different sheet. However, rows may be added to the other sheet so I need that part of the formula to be relative.
On my Summary Sheet I have the formula:

=INDIRECT(C7&"!I"&18)

C7 Is the name of a sheet: "Data1"
I18 contains my value to pick up

This works fine. However If i add a row to my Sheet Data1 my indirect formula stays the same and continues to look to I18 - my data has moved to I19 though.

This spreadsheet has lots of sheets all needing to be looked up and summarised on the Summary sheet.

Anyone come accross this.

love some helo

thanks
Sally

[JBeaucaire]

Relative references like that only work on the current sheet, or in non-INDIRECT connections to other sheets.

The whole point of INDIRECT is to not have to go to 30 individual sheets to create these reference links.

So, when you add a new row to your existing source sheets, you have to add that to ALL the source sheets to keep them identical. And then you will have to adjust your INDIRECT formula manually in at least one cell...but then you can just copy that across to put that change into the other similar INDIRECT spots, right?

[JBeaucaire]

TIP:

If you're not through designing your source sheets, wait on designing your tally sheets.

Or, in your source sheets, consider putting the some of the key numbers you know you're going to tally elsewhere near the TOP of the source sheets, so as you add logic and rows below, that data stays put.

[martindwilson]

you could indirectly reference a cell in the data sheet that is unlikely to move say data1!a1
and a1 in turn references l18 (=l18)
then if rows are added after row A the reference will auto adjust