asked on

Maintaining Cell Links when rows are deleted or cut/pasted in Excel

Hello-

I am trying to link cells between 2 worksheets in the following way....

Data is in Sheet1, Cells in Sheet2 also display the same information as cells in Sheet1....

If the user deletes and/or cuts Row27 from Sheet 1, I want the linked cell in Sheet2 to update based on the new information in Cell A27 (ie. what was in cell A28)....right now, I just get the #REF! error because there is not longer the link....same thing goes if I move Row27 down below Row30...I want the order on Sheet 2 to automatically update....

Can you help?

Thanks,
cm

pauloaguia

=INDIRECT("Sheet1!A27")

You won't be able to fill this formula to the other cells and have to paste it manually though.

pauloaguia

Actually if you use this one instead it will probably do the trick as well and can be copied.

=INDIRECT("Sheet1!R" &ROW() &"C" &COLUMN(),FALSE)

INDIRECT is a reference function that transforms a string into a cell reference. the second argument (that false) indicates if the formula is in A! style notation (true) or R1C1 style (false).

The ROW and COLUMN functions give you the row and column numbers of the cell you're in. If, as you say, the cells addresses will be the same in both sheets, you can use this formula like so and copy it all over the sheet if you want to (it can become slow though :))

Hope this helps

Paulo

Dave

This is not an attempt at the answer, it is a variation on paulo's suggestion

I like using
A1 = INDIRECT(CONCATENATE("Sheet1!",CELL("address",Blank!C12))
which gives A1 = Sheet1!C12

Note "Blank" is an unused sheet that will not have row or column changes

INDIRECT("Sheet1!R" &ROW() &"C" &COLUMN(),FALSE) works but locks you in to the current cell. The Cell("address") method provides some flexibility for setting up formulas

Cheers

Dave

ASKER CERTIFIED SOLUTION

Steve Knight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

I just used a calc speed test, using http://www.erlandsendata.no/english/downloads/tools.php

The calculations in milliseconds were as follows.

=INDIRECT("Sheet1!A27") 0.083
=INDIRECT("Sheet1!R" &ROW() &"C" &COLUMN(),FALSE) 0.102
=INDIRECT(CONCATENATE("Sheet1!",CELL("address",A5))) 0.029
=INDEX(Sheet2!$A:$Z,ROW(),COLUMN()) 0.055

Somewhat suprising. I'm going to test these four approaches in a big sheet manually.

Note, my method only works on the current sheet as I need to remove the sheet name from the address function if I select a cell off the current sheet.

Cheers

Dave

Steve Knight

I suppose for fullness you might aswell test:

=INDIRECT(CONCATENATE("Sheet1!R",ROW(),"C",COLUMN()),FALSE)
=INDIRECT("Sheet1!" & CELL("address",A5))
(I think!)

Looks like your approach is the fastest anyway Dave... curious what the results show now...

Steve

clmoser6

ASKER

Thanks....this one's working great and I actually understand it! Thanks for your help.

Cindy

Steve Knight

Feeling bad now... I was only really only offering another alternative to the answer already there. Paulo / Dave, would you accept some points as apology for nicking this one from under you (silly question I suppose)!

For now, Dave perhaps you could post the results of your speed tests here:

https://www.experts-exchange.com/questions/20563825/points-for-brettdj.html

Thanks for the points & grade though Cindy!

Steve