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.
.
yes, i mean aggregate – sorry for my imprecision
well, yes, but there are operational reasons to have weekly spreadsheets in different places, generated by part-time cashiers who don’t need the aggregate data and are easily confused if there are more than 7 tabs in a spreadsheet. Of course a database would be better, but I don’t really need one, and I don’t want to spend my time maintaining it. But IFERROR() is the answer! Thank you!
… and I thought you would create 52 tabs.
.
My approach is 2 tabs: One for all data and a second wich has only the filtered data from the current (or any other) week. As a second step I can replace the data-sheet with a database-range connected to a query in my database.
You have to enter data either in your spreadsheet or in a database, so this should be the same - but the decision is yours…
There are several possible functions you could use; IFERROR() ISERR() or ISERROR()
Thank you!!
Note that IFERROR() suppresses all errors, which may be ill-advised.
You could test ERROR.TYPE(Week17.B7) for 4 (Excel interoperable) or ERRORTYPE(Week17.B7) for 524 (Calc specific), but you’d also have to cater for the #N/A return value if there is no error, e.g. altogether something like
=IF(IFNA(ERROR.TYPE(Week17.B7);0)=4;0;Week17.B7)