#### Yepper

##### Board Regular
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.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.

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?

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.

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.

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!

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.

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!

Replies
1
Views
455
Replies
5
Views
813
Replies
0
Views
234
Replies
4
Views
1K
Replies
29
Views
1K

1,203,082
Messages
6,053,420
Members
444,662
Latest member
AaronPMH

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

### Which adblocker are you using?

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

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