Help with referenced sheet

[Bill L]

Hi all,

I am attempting to feed data into a cell on sheet ABC that comes from
another sheet that will be created programmatically (call it sheet XYZ). I
start by creating the reference with sheet XYZ already existing, so that I
can pick the cells I want off of it. I then delete sheet XYZ. My cells on
ABC all then become #REF, which is okay with me. I save and close. I then
run the program that takes some data, opens my excel file, and (re)creates
sheet XYZ and puts the data I want onto it.

The problem is that all of my cells on ABC that reference XYZ keep the #REF
until I double click in the cell and hit 'Enter'. Neither Refresh or
Calculate Sheet work.

Any ideas?

Thanks!

Bill

 

[ShaneDevenshire]

Hi,

If you delete the sheet xyz then Excel looses those references and you can't
get them back just by creating a new xyz sheet. I would suggest that you
keep a sheet xyz1 to which the reference refer. Then when the new xyz sheet
is created you either copy the data from it to the xyz1 sheet or you use a
Find and Replace to replace all xyz1! references in sheet abx with references
to sheet xyz!.