I am looking for a way to make an INDIRECT formula ignore errors and print a 0 instead. I have it working in a round about way but would like it neater.
I have an INDIRECT formula to load a cell from a seperate worksheet
=INDIRECT("'Invoice (2)'!A1")
The reason I've used an INDIRECT is so that when the sheet named 'Invoice (2)' is not available (i.e. I've temporarily deleted it) it does not change my formula.
However. When there is NO 'Invoice (2)' sheet, I get the error: #REF!
What I would prefer is the result to be '0'.
I have a work around by hiding this field and then referencing it in an AGGREGATE field
=AGGREGATE(9,6,N19) - The 6 ignores any errors and puts a '0' in place and this works perfectly.
So I guess I am just curious if there is a way to combine the two as to make it neater. You would think there would be an 'ignore error' for other commands other than aggregate.
I have tried =AGGREGATE(9,6,INDIRECT("'Invoice (2)'!A1")) and the script finds the data fine but it does not ignore errors like it should.
