Hide #VALUE! in result of array formula
I am using the following array formula in an Excel spreadsheet:
{=INDIRECT(ADDRESS(MAX((ROW(30:34)*(A30:A34<""))) ,COLUMN(A:A)))}.
The idea is to show the last non blank amount in a report. The problem is,
if all cells are left blank, my report shows #VALUE!, which I would like to
hide. I have tried some IF( statements, but if any cells are left blank it
shows a blank on the report even if one value is there. I looked at
conditional formatting to turn the fonts white when #VALUE! or ISERROR
conditions are there. Didn't work. There is probably an easy fix, but I can't
seem to figure it out.
Thanks
|