Solved

Replacing tabs in excel when those tabs are referenced in formulas

Posted on 2013-06-18
3
211 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 200 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

773 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