Deleting a worksheet but retaining values from the worksheet.
 

Hi all, I have a problem that will probably be deceivingly easy or
suprisingly difficult to fix...

In a workbook, where "Sheet 1" has formulas like =5*Sheet2!A1 and
=5*Sheet3!B2

I would like to be able to delete "Sheet 2" so that anywhere in "Sheet
1" where a formula referenced "Sheet 2", the reference is replaced with
the value - while leaving formulas for still-present worksheets (like
"Sheet 3").

For example, if "Sheet2!A1" = 5 and "Sheet3!B2" = 10 and then I delete
"Sheet 2", the "Sheet 1" formulas I mentioned above would do this:

**"Sheet 1" before deleting "Sheet 2"
In one "Sheet 1" cell: =5*Sheet2!A1
In another "Sheet 1" cell: =5*Sheet3!B2

**"Sheet 1" after deleting "Sheet 2"
In one "Sheet 1" cell: =5*5
In another "Sheet 1" cell: =5*Sheet3!B2

So I can't use "Paste Values" because it would paste the values for all
the formulas. I just want Excel to replace the no-longer-valid-links
after deleting a worksheet with the values it last pulled in.

Has Excel got a function to do this? Or does anyone know a clever way
to do it?

Thanks in advance!

Nick

Deleting a worksheet but retaining values from the worksheet.
 

Hi,

you can change part of formula with value by selecting this part (in your
first example select Sheet2!A1) and press F9. It will replace it with value
from Sheet2!A1.
For changing automatically lot of such formulas you need to write macro...
or find/buy some add-ins, like
http://www.digdb.com/excel_add_ins/f...s_broken_find/.

Regards,
Nika Lampe

" je napisal:

Hi all, I have a problem that will probably be deceivingly easy or
suprisingly difficult to fix...

In a workbook, where "Sheet 1" has formulas like =5*Sheet2!A1 and
=5*Sheet3!B2

I would like to be able to delete "Sheet 2" so that anywhere in "Sheet
1" where a formula referenced "Sheet 2", the reference is replaced with
the value - while leaving formulas for still-present worksheets (like
"Sheet 3").

For example, if "Sheet2!A1" = 5 and "Sheet3!B2" = 10 and then I delete
"Sheet 2", the "Sheet 1" formulas I mentioned above would do this:

**"Sheet 1" before deleting "Sheet 2"
In one "Sheet 1" cell: =5*Sheet2!A1
In another "Sheet 1" cell: =5*Sheet3!B2

**"Sheet 1" after deleting "Sheet 2"
In one "Sheet 1" cell: =5*5
In another "Sheet 1" cell: =5*Sheet3!B2

So I can't use "Paste Values" because it would paste the values for all
the formulas. I just want Excel to replace the no-longer-valid-links
after deleting a worksheet with the values it last pulled in.

Has Excel got a function to do this? Or does anyone know a clever way
to do it?

Thanks in advance!

Nick