Removing a worksheet from a linked formula (Excel 2007)

July 17, 2013 at 09:58:39
Specs: Windows XP
I have 10 worksheets that are my base and an 11th that is a roll-up with links/formulas based upon previous 10. I was informed to remove one of the spreadsheets as we will no longer use that business line. Is there an easy way to remove that spreadsheet link from the rollup one without changing the hundreds of formulas in the roll up sheet (needed all the formulas due to weighted averaging issues).

See More: Removing a worksheet from a linked formula (Excel 2007)

Report •

July 17, 2013 at 12:40:11
Could you give us an example of the formula(s) you need to change so we know what we are working with? A "before" and "after" would be nice.

Just tossing this out there....could you delete all of the data in the unused worksheet and then hide it? Would that allow you to leave all of the formulas as is and have them just return 0 when they reference the empty sheet? Just a thought...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 17, 2013 at 12:54:11
The formulas are about 4,000-6,000 characters in about 500+ cells with links throughout the 10 worksheeets. The issue I have is that I need to now delete one of those worksheets (that line of business is now no longer offered) and thus everywhere it is linked throughout my roll up 11th worksheet.

I tried to crtl-H change from - to but the addition of the cell reference makes that attempt moot. I also tried to shorten the "from" in Ctrl-h without the cell reference and add a * then change the cell to 0 but it changes all my formula after the reference in found to 0 not just that worksheet reference.

I could rewrite all the formulas for all cells but that will take weeks and then you have the issue of mistakes and verification.

There has to be a command that can find and eliminate a reference to another worksheet without using the cell designator so that I can make that a 0 value.

Your suggestion of making the unused worksheet a 0 value sounds good but the reports are pulled from the system and thus that worksheet will not exist any longer. Also this may happen again so trying to learn a better way than recreating the roll-up.

Report •

July 17, 2013 at 13:23:26
Without knowing what your formulas look like,
try this:

On your sheet use the CTRL-~ (Control - Tilde Character)
That will display all of the formulas on the current sheet

Then, would it be possible to do a Search & Replace?
You may need to use Wildcards or do several passes,
but it might work.


Report •

Related Solutions

July 17, 2013 at 15:42:46
As Mike hinted at, we can't see your worksheet from where we're sitting so we have to try and imagine your issue based solely on your words.

Is it not possible to Copy and Paste a formula or 2 into a post and then explain what needs to be changed? For all we know, it may be possible to make the changes with VBA code. We may also see something that gives us a clue about what to go research and/or test.

Right now,I don't think we have enough information to offer anything other than what we already have - but we do want to help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question