#REF in Excel formula

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
Hi

Any trick to keep excel formula as it is even if look up sheet get deleted.

=COUNTIFS(Sheet1!$D:$D,">="&TODAY()-1) changes to
=COUNTIFS(#REF!$D:$D,">="&TODAY()-1) when I delete Sheet1

Please have a look


Thanks
A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try

=COUNTIFS(INDIRECT("Sheet1!$D:$D"),">="&TODAY()-1)
Rob HensonFinance Analyst

Commented:
To avoid error results, I would suggest enclosing in an IFERROR statement:

=IFERROR(COUNTIFS(INDIRECT("Sheet1!$D:$D"),">="&TODAY()-1),0)

Author

Commented:
Thank you Saqib for your suggestion, that worked for me.

Good suggestion Rob, I want to see if any thing goes wrong.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial