formula and delete sheet

[bijan]

Hi all,
I have sumif formula:
SUMIF(TST!F10:F20000;D2;TST!G10:G20000)
if i delete tst sheet my formula loss its refrences, how can i prevent ref!
error and formula returns zero if the sheet deleted
Thanks
Bijan

 

[Don Guillett]

You have it set to refer to the sheet you are deleting. What do you want it
to do???

 

[bijan]

Hi Don,
simply I want my formula in the active sheet shows zero if tst sheet is not
exist and if the tst sheet is exist it calculate the related data,
because the txt sheet created by query frequently
Many Regards
Bijan

 

[Dave Peterson]

=IF(ISERROR(CELL("Address",INDIRECT("'tst'!A1"))),0,
SUMIF(INDIRECT("'TST'!F10:F20000"),D2,INDIRECT("'TST'!G10:G20000")))

I use the comma for my list separator, you'll want to convert them to
semicolons:

=IF(ISERROR(CELL("Address";INDIRECT("'tst'!A1")));0;
SUMIF(INDIRECT("'TST'!F10:F20000");D2;INDIRECT("'TST'!G10:G20000")))

(I think I got them all!)