Changing Cell References
November 20, 2001 - by Bill Jelen
Scott sent in this excellent problem, which he had 98% of the way solved:
I have two spreadsheets. One of which, I enter data into. The other is a mirror of the first using the paste link feature. This works great. You can 'see' the first sheet by looking at the second sheet.
Under perfect circumstances, sheet 2, cell A10 shows me the data in sheet 1 cell A10. Now, let's say that there is a value of "a-ten" in cell A10 of sheet 1. If I insert a row into the sheet 1 between rows 9 and 10, this bumps A10's value of "a-ten" to A11.
Looking at my second sheet, cell A10 still shows "a-ten", but when you look at the address, the reference is now A11. Cell A9 will be referencing A9 as it should. But, it essentially created a vacuum where data from sheet 1 will fall through the cracks.
Excel is tracking the data that used to be in cell A10, not what is actually in A10.
I would like to track what is physically in A10. not where the data in A10 goes.
My idea to beat Excel at it's own game is to somehow create a reference that uses a combination of ROW and ADDRESS functions like this.Placing this in the second sheet
=(ADDRESS(ROW(),1,2,,"='[workbook]sheet1'!"))gives me the text of the reference I want. Is there any way to convert that into an actual reference?
The INDIRECT() function will take text that looks like a reference and will give you the actual value at that reference, but this will not work when pointing to another worksheet.
How about using OFFSET function?
=OFFSET(Sheet2!$A$1,ROW()-1,0)
