Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep cell reference
hi,
i have a cell in sheet_new with a formula in cell A1: = sheet1!C2 i would like to know how the cell A1 can keep this formula even if sheet1 is deleted, because it may be possible to delete the sheet1 and later add it back, so when it is added back, i want the formula remembered/restored. thanks in advance, geebee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep cell reference
This formula should work for you...
=IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1")) -- HTH... Jim Thomlinson "geebee" wrote: hi, i have a cell in sheet_new with a formula in cell A1: = sheet1!C2 i would like to know how the cell A1 can keep this formula even if sheet1 is deleted, because it may be possible to delete the sheet1 and later add it back, so when it is added back, i want the formula remembered/restored. thanks in advance, geebee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep cell reference
basically just set up a macro that adds a sheet and then the formula
maybe give some more info as to what you're doing, if this doesn't help Sub Add_Sheet() Worksheets.Add after:=Worksheets(Worksheets.Count) Range("A1").Formula = "=Sheet1!C2" End Sub -- Gary "geebee" (noSPAMs) wrote in message ... hi, i have a cell in sheet_new with a formula in cell A1: = sheet1!C2 i would like to know how the cell A1 can keep this formula even if sheet1 is deleted, because it may be possible to delete the sheet1 and later add it back, so when it is added back, i want the formula remembered/restored. thanks in advance, geebee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep cell reference
Unless sheet1!a1 contains an error <vbg:
=IF(ISERROR(CELL("Address",INDIRECT("'Sheet1'!A1") )), "No Sheet1","yourformulahere") Jim Thomlinson wrote: This formula should work for you... =IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1")) -- HTH... Jim Thomlinson "geebee" wrote: hi, i have a cell in sheet_new with a formula in cell A1: = sheet1!C2 i would like to know how the cell A1 can keep this formula even if sheet1 is deleted, because it may be possible to delete the sheet1 and later add it back, so when it is added back, i want the formula remembered/restored. thanks in advance, geebee -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep cell reference
it is not working. nothing is happening. i even tried =INDIRECT(TEST1!F20)
and i get a #REF! error. "Jim Thomlinson" wrote: This formula should work for you... =IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1")) -- HTH... Jim Thomlinson "geebee" wrote: hi, i have a cell in sheet_new with a formula in cell A1: = sheet1!C2 i would like to know how the cell A1 can keep this formula even if sheet1 is deleted, because it may be possible to delete the sheet1 and later add it back, so when it is added back, i want the formula remembered/restored. thanks in advance, geebee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
keep cell reference
Watch your double quotes:
=indirect("test1!f20") geebee wrote: it is not working. nothing is happening. i even tried =INDIRECT(TEST1!F20) and i get a #REF! error. "Jim Thomlinson" wrote: This formula should work for you... =IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1")) -- HTH... Jim Thomlinson "geebee" wrote: hi, i have a cell in sheet_new with a formula in cell A1: = sheet1!C2 i would like to know how the cell A1 can keep this formula even if sheet1 is deleted, because it may be possible to delete the sheet1 and later add it back, so when it is added back, i want the formula remembered/restored. thanks in advance, geebee -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions |