Formula Help with #REF!

[OdAwG]

I was wondering if there is a way to save my formula that references
another
sheet from going to #REF when the sheet is deleted and then recreated.

In my main sheet called Master, I have the following formula in G1
=Roster!$D$8

When ever my Roster sheet gets deleted and rebuilt, my formula above always
goes to the following
=#REF!$D$8.

If it is possible, how can i prevent this from happening because now what I
have to do is go to every cell and change it form
=REF!$D$8 to =Roster!$D$8 to pull my values over.

Any and all help is greatly appreciated.

Argus

 

[Guest]

One way is to use INDIRECT, viz:
=INDIRECT("'Roster'!D8")

 

[Guest]

try

=INDIRECT("Roster!D8")

although if cell D8 gets moved (by inserting/deleting rows or cut/paste)
Indirect will still reference cell D8 - it does not update when the target
cell moves.

alternatively, you could go to your master sheet and use find/replace to
replace = with something like %%%. Then delete your roster sheet and
recreate it. Then use find/replace to replace %%% with =.