Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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? =OFFSET(Sheet2!$A$1,ROW()-1,0).

By Bill Jelen on 20-Nov-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.