• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
clmoser6
Asked:
clmoser6
  • 3
  • 2
  • 2
  • +1
1 Solution
 
pauloaguiaCommented:
=INDIRECT("Sheet1!A27")

You won't be able to fill this formula to the other cells and have to paste it manually though.
0
 
pauloaguiaCommented:
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
0
 
Dave BrettVice President - Business EvaluationCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Steve KnightIT ConsultancyCommented:
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:

=INDEX(Sheet2!$A:$Z,ROW(),COLUMN())

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...

Steve
0
 
Dave BrettVice President - Business EvaluationCommented:

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




0
 
Steve KnightIT ConsultancyCommented:
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
0
 
clmoser6Author Commented:
Thanks....this one's working great and I actually understand it!  Thanks for your help.

Cindy
0
 
Steve KnightIT ConsultancyCommented:
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:

http://www.experts-exchange.com/Applications/MS_Office/Q_20563825.html


Thanks for the points & grade though Cindy!

Steve
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now