Excel 2010 'renumbering' forumla values on linked worksheets

Try this instead:

=VALUE(INDIRECT("Data!AC" & ROW())

This assumes that row 1 of data sheet goes to row 1 of other sheet. If not put +n between the last brackets with n being the number of rows that they are different.

Thanks
Rob H

Hmm I've never used INDIRECT before (although I've seen it posted online in places). Do I need to reference my cell number when i do the "Data!AC"?

The row numbers don't match up. So Data!AC5 would match up to [other worksheet]!BH789

Would that be  =VALUE(INDIRECT("Data!AC" & ROW()+784)?

Thanks!

Sorry, in that case it should be -n so for your example it would be:

=VALUE(INDIRECT("Data!AC"&ROW()-784))

or

=VALUE(INDIRECT("Data!AC"&ROW(BH5))

To expand on the original question:

Why is it happening? in your example, Contents of Data!AC5 are linked to OtherSheet!BH789. New data is inserted into the data sheet by inserting rows. Excel has a link to row 5 but row 5 has just been pushed down to row 6 by inserting a row above it, therefore the link moves.

How does INDIRECT work?  A simple formula of =A1 will return the contents of A1. Whereas =INDIRECT(A1) will look at the text contents of A1 and evaluate the contents as a cell address and return the contents of that cell instead. If the text contents do not evaluate to a cell address it will return an error.

So the formula above is forcing the "Data!AC" part of the cell address as a text string and then ROW() returns the row number of the cell reference within the brackets or if blank the row of the cell in which it is placed. Thus completing the cell reference.

Thanks
Rob H
Rob H

Oh wow that makes so much sense! Thank you for explaining that.

I have a row like this:

=VALUE(SUM('Data'!H2:I2))

How would I convert that to using the INDIRECT? (Or does it need to be two steps)

This approach is somewhat volatile as it relies on the structure of the 'OtherSheet' remaining static.

Is there any other reference that could be used to identify the relevant row from the source data, eg lookup on another column.

Thanks
Rob H

The other sheet will be static - it's just a linked table in from SQL server. There's really no lookups - just the two fields there that need summed. Would putting them in their own field on the "OtherSheet" be the best approach?

I assume it would be the 2 in the formula that might change.

=VALUE(SUM(INDIRECT("Data!H"&ROW()&":I"&ROW())

With the adjuster after the ROW() if so required.

Thanks
Rob H

Oh wow - thank you so much! This makes a lot of sense!