Linked formula does not follow source data that is moved

Yepper

Board Regular
Joined
Apr 18, 2002
Messages
68
Hello. I am trying to modify someone else's file, and I am encountering an unusual problem. Let me explain with an example.

Let's say there is a "Total" tab with formulas in line 10, columns A to F, that adds up all of the corresponding values in line 10, columns A to F, on tabs X, Y, and Z. All four tabs reside in the same file.

Next, I select all four tabs, and then MOVED the entire line 10 to line 5. At this point, shouldn't all the formulas on the Total tab now point to line 5 on tabs X, Y, and Z? Instead, on this particular file, the formulas still point to line 10.

Does anyone have any idea as to what can be causing this? I performed several tests on a new file and it seems like this should not be happening.

Thanks for your help!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Does the formula on the total sheet in column A look like this

=SUM(X:Z!A10:A10)

or like this

=X!A10+Y!A10+Z!A10

The 1st one won't update with the new cell reference. The 2nd version should update so you could retype the formula if this is the problem. There may be an option somewhere, but I've never found it. Sorry, not the greatest answer.
 
Upvote 0
Thanks, Marnie. Actually, the formula looks like this: =SUM(X:Z!A10)

On a simeple worksheet, when I selected the 3 input tabs and then moved the input line from row 10 to row 5, the formula on the Total tab should change to =SUM(X:Z!A5) ...and it does. However, on my colleague's large file, the same thing does not happen (ie., the formula does NOT change). I'm just trying to find out what could be causing this.

Any thoughts?
 
Upvote 0
Sorry, I couldn't get it to work on my computer. I tried setting up a very simplified spreadsheet and using the =sum formula, I couldn't get the formula to revise when I moved the targets. I couldn't find any specific options to turn on or off. Is it possible that the large file is in a different version of excel? Maybe different versions don't update the same.
 
Upvote 0
Hi, the purpose of moving is to preserve the original link. If you want to change the references when you move, do a copy and paste, and then delete the original cell. This is my simple solution. Maybe others will have a more sophisticated one. :cool:
 
Upvote 0
Hi, simonf. Your explanation is exactly why I moved the source data (instead of copy-and-paste) -- so that the links would be preserved. However, on this particular file where I moved all at once the source data on the same line on about 20 tabs, the related summation formula continued to point back at the old line location -- not at the new line where I moved the source data too. This is very odd!
 
Upvote 0
I am suspecting there is a hidden sheet in your large file. For instance if sheet Y is hidden and hi-lite X and Z only, X:Z linked cell in Total tab will not follow as you have fail to hi-lite full X,Y,Z. On menu bar, goto Format, Sheet, Unhide to see what is being unhidden.
 
Upvote 0
Hello, XIGuru. You are indeed a guru with Excel! Your hunch was correct. In my colleague's file, there were worksheets hidden within the range of tabs that I selected. Once I unhid them, everything worked fine -- the links were preserved and they followed the new location of the source data that were moved...as expected.

A huge thank you to you and all the others who helped me. You people truly make this a terrific site for Excel users!
 
Upvote 0

Forum statistics

Threads
1,223,579
Messages
6,173,171
Members
452,503
Latest member
AM74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top