CODEWITHSUNDEEP
vbaexcelformula
Keva161
Keva161

Reputation: 2693

Return blank if #VALUE is returned

I've setup a formula that combines numbers from multiple worksheets and then compares it against another number from worksheet.

I am using the INDIRECT function to reference the sheets as well as COLUMN and ROW to adjust the numbers to the corresponding coordinates when I drag it across.

However, some of the figures don't always exist in the worksheets so a #VALUE error is returned. How Can I change it so a blank cell is shown if this happens?

My Current formula: =INDIRECT("'"&$C$11&"'!R"&ROW(E29)&"C"&COLUMN(E29),FALSE)-SUM(INDIRECT("'"&$C$11&"'!R"&ROW(C29)&"C"&COLUMN(C29),FALSE),INDIRECT("'"&$C$13&"'!R"&ROW(E29)&"C"&COLUMN(E29),FALSE))

Upvotes: 1

Views: 32948

Answers (1)

Bathsheba
Bathsheba

Reputation: 234705

Use =IFERROR(<your original formula>, "") which will replace any error with "" but passes any other result through.

But do bear in mind the degradation in spreadsheet stability: INDIRECT makes spreadsheets brittle enough on its own: your hiding any error output could be dangerous.

Upvotes: 3

Related Questions