Sheet names replaced with #REF in formulas

ninjaglowskulls

New Member
Joined
Sep 7, 2011
Messages
2
Hi,
I'm hoping someone can help me out with this. I have a workbook with cell references to multiple sheets in another workbook.
ie. ='Path of workbook\[workbookname.xls]sheetname1'!A1
All the links worked and pulled the correct data when I set them up. However, when I saved, closed and reopened the file later, some of the sheet names were replaced by #REF (about 10/150). The original links were set up by putting "=" in the destination cell, then clicking on the source cell in the source workbook, so they should all still work. I've tried resetting the links using the original method, manually replacing the #REF with the correct name. I've double check the link status in Edit->Links and the results were OK.

I'm stumped and was hoping someone could point me in the right direction for troubleshooting this issue. I don't want to delete the source sheets and recreate them (don't even know if that would work).

Any help would be greatly appreciated.

Thanks.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
So it won't let me edit my post, but I found the solution.

It turns out Excel will only look at a limited number of worksheets for external links in other workbooks. I think it's around 200 or so. I moved the source sheets for the broken links to the front of the workbook and, voila, it worked.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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