Solved

Replacing tabs in excel when those tabs are referenced in formulas

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now