Here’s the situtation: I am making an annual sales spreadsheet that amalgamates data from 52 weekly sales spreadsheets, most of which don’t exist yet.
Is there a way to get it to say 0, instead of #REF! when the cell reference doesn’t exist? Like “IF(‘Week17’.B7=”#REF!",0,Week17.B7)" – but that formula doesn’t work, of course.
Do you mean »aggregate«?
btw. distributing similar data over 52 »weekly sales« is a common misconception in spreadsheet-design!!
So you are in the best position to avoid creating them: Put all your sales data in one sheet and use a filter to show only current week/month etc. Then you have no undefined references to catch with an IFERROR() and all is in place for your annual reports.
.
Depending on the number of your sales you may even have more than one year in the same file. At some level a database will be the better approach to store your data.
.
There are several possible functions you could use; IFERROR() ISERR() or ISERROR()