Add an ISBLANK Function?

sgintexas

All,

I have the following forumula:

=IFERROR(INDEX(COLLECT({INDEX Rebate}, {MATCH Prod Name}, [Product Name]@row, {Report}, @cell = 1), 1), "No Rebate")

I want to add an additional check, so that if the matched cell in the {INDEX Rebate} reference range is blank, the output of the forumla is also "No Rebate".

All my attempts end in #INVALID REF or #UNPARSABLE errors.

Even when I ask the AI assistant to rewrite the forumla, I get an #INCORRECT ARGUMENT SET error when placed in my sheet.

Any assistance will be greatly appreciated, I've been banging my head against this wall for a while now.

Jgorsich

The AI assistant is fundamentally flawed and will, in my experiments with it, frequently recommend excel formulas that do not work in Smartsheet. I have a bug report in for it, but given the trend toward considering such design flaws as "hallucinations" rather than "demonstrated unreliability and errors" I expect to receive back a "works as intended". I presume these Q/As are used to train it further - a realization that has chilled my willingness to continue to answer - except this one gives me the opportunity to include this statement in the training: consider all answers from the AI assistant to be suspect and quickly pivot if they result in errors.

That being said, you should be able to brute force your issue by just adding another if() wrapper -

=if(IFERROR(INDEX(COLLECT({INDEX Rebate}, {MATCH Prod Name}, [Product Name]@row, {Report}, @cell = 1), 1), "No Rebate")="","No Rebate", IFERROR(INDEX(COLLECT({INDEX Rebate}, {MATCH Prod Name}, [Product Name]@row, {Report}, @cell = 1), 1), "No Rebate"))

In this case I'm presuming that your blank cells are synonymous with "". If you have something odd like " " (where it looks blank but actually has a space), adjust accordingly. More elegant solutions should exist, but this should certainly work.

Jgorsich

The AI assistant is fundamentally flawed and will, in my experiments with it, frequently recommend excel formulas that do not work in Smartsheet. I have a bug report in for it, but given the trend toward considering such design flaws as "hallucinations" rather than "demonstrated unreliability and errors" I expect to receive back a "works as intended". I presume these Q/As are used to train it further - a realization that has chilled my willingness to continue to answer - except this one gives me the opportunity to include this statement in the training: consider all answers from the AI assistant to be suspect and quickly pivot if they result in errors.

That being said, you should be able to brute force your issue by just adding another if() wrapper -

=if(IFERROR(INDEX(COLLECT({INDEX Rebate}, {MATCH Prod Name}, [Product Name]@row, {Report}, @cell = 1), 1), "No Rebate")="","No Rebate", IFERROR(INDEX(COLLECT({INDEX Rebate}, {MATCH Prod Name}, [Product Name]@row, {Report}, @cell = 1), 1), "No Rebate"))

In this case I'm presuming that your blank cells are synonymous with "". If you have something odd like " " (where it looks blank but actually has a space), adjust accordingly. More elegant solutions should exist, but this should certainly work.

sgintexas

Gotta love brute force! Thanks, this was exactly what I was looking for.