?
Solved

Replacing tabs in excel when those tabs are referenced in formulas

Posted on 2013-06-18
3
Medium Priority
?
221 Views
Last Modified: 2013-06-23
I have a tab in my excel workbook that other tabs reference.  I have to replace this tab each week with the current version.  i can either copy/paste the data from the new tab into the existing one.  Or I can copy the entire tab from the new spreadsheet into the workbook and ensure the tab names are correct.  I can do either.

Here's my problem.  As soon as I delete the tab/data that the formulas were using, the entire spreadsheet that referenced any of those cells return "REF!" in the formulas.  

Can I turn off automatic calculate or something like that to prevent this from happening each week?
0
Comment
Question by:Feisty472
3 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39258072
One way is to

copy the tab to the workbook first
then perform a search/replace for the tab names from the old one to the new one
and then delete the old tab.

This may cause a problem if your formula contains the old tab name as text string etc which will also get changed.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 800 total points
ID: 39260844
I am assuming you keep a copy of the current tab in the file for archive purposes but want your data to reference the new one.  Try this:

1.

Copy the entire current tab (not using copy cells, but by right clicking on the tab and using Move or Copy... or ctrl-click and dragging.

2.

Give the copied tab whatever name you want to give it

3.

Paste your new data over the current tab (not the copy) using normal copy/paste - the cells that reference the current data will not be affected

4.

You could now give the current tab a new name (right click on tab and select Rename, or double click on the tab name and overtype) if you have it named with the current date or something similar- this will change any reference to the tabIt would probably help if you could post the file or more details, as this is just guessing your requirements
0
 

Author Closing Comment

by:Feisty472
ID: 39270127
I did this a little differently.  But, it got the job done.

Thanks
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

601 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