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

Posted on 2003-03-25
Medium Priority
Last Modified: 2006-11-17

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?

Question by:clmoser6
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1

Expert Comment

ID: 8206902

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

Expert Comment

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


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

LVL 50

Expert Comment

by:Dave Brett
ID: 8207260
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


Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

LVL 43

Accepted Solution

Steve Knight earned 2000 total points
ID: 8208470
The mother-in-law discovered this feature recently... carefully CUTting and pasting rows from a source sheet rather than COPY and Paste like she'd been shown and gradually mucking up the destination... it was locked down and protected to the nth degree... but never through of that...

I tried suggesting just using Copy / Paste instead but I ended up using a variation on this:


Nothing wrong with either of the other ways mind, all three of them are probably a lot slower than a direct link but if they work for your app...

LVL 50

Expert Comment

by:Dave Brett
ID: 8208614

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.



LVL 43

Expert Comment

by:Steve Knight
ID: 8210098
I suppose for fullness you might aswell test:

=INDIRECT("Sheet1!" & CELL("address",A5))
(I think!)

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


Author Comment

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

LVL 43

Expert Comment

by:Steve Knight
ID: 8210698
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:


Thanks for the points & grade though Cindy!


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question