Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i know that my worksheet having repeated same values | Excel Worksheet Functions | |||
Regarding updating 1000 values using vlookup from another worksheet | Excel Worksheet Functions | |||
Deleting filtered out data from a worksheet | Excel Worksheet Functions | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) |