Change Sheet Referred to in Multiple Formulas - XL2010

[Excelerometer]

Originally I had a large sheet (named MASTER LIST) and an analysis sheet (named ANALYSIS). The Master List ended up with the million plus rows issue that I could not resolve. To address this problem, I copied and pasted into a new sheet (named LIST) my entered data plus a few thousands rows for future entries. I would like to remove the over-sized Master List so the file is smaller and runs faster; however, I cannot figure out to change the formulas in the Analysis page to reference the LIST sheet instead of the Master List. There are SEVERAL formulas, many of which reference the Master List multiple times within the formula itself.

I have tried the following:

1. On the Analysis sheet, selected Find & Replace, tried to replace "MASTER LIST" with "LIST". This did not work, I get the NAME? error.
2. I also tried just swapping the names of the sheets (turned List into Master List after renaming the original Master List to something else), but that clearly was not a solution.

 

[Jonmo1]

Try like this...

Rename List to something else, say "New List"
Rename Master List to List
Use the Find/Replace tool to replace = with #= (make sure to set it to look at entire workbook)
Rename List back to Master List
Rename New List back to List
Use the Find/Replace tool to replace #= with =


Hope that helps.

 

[Excelerometer]

The issue I am running into is that there is SO much data (really just empty rows), that when I try to replace "=" with "#=" excel freezes up. Could I simply DELETE the original Master List before performing this task?

 

[Jonmo1]

No, that would make all formulas that refer to it turn into #Ref! Errors.

Just be patient, it may appear excel is frozen, it's not....just wait..Go get lunch or something.
If it's a one time fix, it's worth the wait.

 

[Excelerometer]

So, I keep getting this message: MS Excel is trying to recover your information...This might take several minutes"

I've tried doing this on multiple computers, including a new one. I don't think it's a matter of waiting it out, as I left it overnight and it didn't work.

Could I complete the steps you mentioned above, but do it in smaller pieces at a time? So either replace only on the sheet, and then on the very large sheet, just highlight the cells that contain data?

 

[Jonmo1]

Maybe if you turn off calculation before you do the find/replace = with #=

 

[Excelerometer]

Turning off calculations definitely helped. I still had to go through the steps one sheet at a time, but it worked like a charm, and I was able to delete the oversized sheet. THANK YOU so much for your help. Problem solved.

 

[Jonmo1]

Great, glad to help.