keep cell reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geebee - 03 Mar 2008 19:08 GMT

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

Reply to this Message

Jim Thomlinson - 03 Mar 2008 19:19 GMT

This formula should work for you...
=IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1"))

Signature

HTH...

Jim Thomlinson

> hi,
>
[quoted text clipped - 8 lines]
> thanks in advance,
> geebee

Reply to this Message

Dave Peterson - 03 Mar 2008 19:39 GMT

Unless sheet1!a1 contains an error <vbg>:

=IF(ISERROR(CELL("Address",INDIRECT("'Sheet1'!A1"))),
"No Sheet1","yourformulahere")

> This formula should work for you...
> =IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1"))
[quoted text clipped - 15 lines]
> > thanks in advance,
> > geebee

Signature

Dave Peterson

Reply to this Message

geebee - 03 Mar 2008 21:44 GMT

it is not working. nothing is happening. i even tried =INDIRECT(TEST1!F20)
and i get a #REF! error.

> This formula should work for you...
> =IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1"))
[quoted text clipped - 11 lines]
> > thanks in advance,
> > geebee

Reply to this Message

Dave Peterson - 03 Mar 2008 22:06 GMT

Watch your double quotes:

=indirect("test1!f20")

> it is not working. nothing is happening. i even tried =INDIRECT(TEST1!F20)
> and i get a #REF! error.
[quoted text clipped - 18 lines]
> > > thanks in advance,
> > > geebee

Signature

Dave Peterson

Reply to this Message

Gary Keramidas - 03 Mar 2008 19:20 GMT

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

Signature

Gary

> hi,
>
[quoted text clipped - 8 lines]
> thanks in advance,
> geebee

Reply to this Message