Solved

Replacing tabs in excel when those tabs are referenced in formulas

Posted on 2013-06-18
3
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 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