Solved

Replacing tabs in excel when those tabs are referenced in formulas

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

20 Experts available now in Live!

Get 1:1 Help Now