IFERROR(INDEX(COLLECT... - display something other than blank for error?

David Acord

I have a formula built for one of my sheets that a Smartsheet expert designed for me, and it works perfectly. The formula, as it stands now, is:

=IFERROR(INDEX(COLLECT({Developer Assigned}, {Row ID}, [Row ID]@row), 1), "")

Currently, it looks at the respective data (the 'developer assigned' and 'row ID' columns) on one sheet, and then displays the relevant data on another sheet provided the row ID matches. This is great!

Now, I am trying to make a tweak - my users are asking for cells that have no data in them to show N/A instead. I tried changing the "" at the end of the formula to "N/A" but it doesn't do anything.

Can anyone suggest a solution? Thanks!

Genevieve P.

Hi @David Acord

Yes, no problem! There's a few ways to do this. I suppose the first question is if you could update the source sheet to show "N/A" instead of blank?

If not, we can repeat the formula. Once to see if it's blank, then a second time if it's not blank, like so:

=IF(formula = "", "N/A", formula)

Like so:

=IF(IFERROR(INDEX(COLLECT({Developer Assigned}, {Row ID}, [Row ID]@row), 1), "N/A") = "", "N/A", IFERROR(INDEX(COLLECT({Developer Assigned}, {Row ID}, [Row ID]@row), 1), "N/A")

Let me know if that works!

Cheers,

Genevieve

Genevieve P.

Hi @David Acord

You should just be able to adjust the "" to "N/A", like you said:

=IFERROR(INDEX(COLLECT({Developer Assigned}, {Row ID}, [Row ID]@row), 1), "N/A")

If you're still seeing blank, is it possible that there is a Row ID on both sheets but that there's no data in the Developer Assigned column of the source sheet?

Cheers!

Genevieve

David Acord

Hi Genevieve, that's the issue, I believe - it's either showing the developer name or there's just no data - do you know of a way to make it display N/A instead of just being blank, in that instance?

Genevieve P.

Hi @David Acord

Yes, no problem! There's a few ways to do this. I suppose the first question is if you could update the source sheet to show "N/A" instead of blank?

If not, we can repeat the formula. Once to see if it's blank, then a second time if it's not blank, like so:

=IF(formula = "", "N/A", formula)

Like so:

=IF(IFERROR(INDEX(COLLECT({Developer Assigned}, {Row ID}, [Row ID]@row), 1), "N/A") = "", "N/A", IFERROR(INDEX(COLLECT({Developer Assigned}, {Row ID}, [Row ID]@row), 1), "N/A")

Let me know if that works!

Cheers,

Genevieve

David Acord

Hi Genevieve,

How folks like you are so talented at formulae boggles my mind, but this worked perfectly! Sorry about the delay in response (the downtime yesterday meant I couldn't test).

Thank you so much!

Genevieve P.

No problem at all! I'm glad we found a solution for you. 🙂