Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Linked cell skewing formula result
I am trying to write a formula that checks for 4 criteria;
a. That the result in cell [Transition]38 = "Transition"
b. That cell [NPS %]38 is not blank
c. That cell [BG KPI]38 is not blank
d. That cell [LE CM %]38 is not blank
If all 4 criteria are met, then YES, otherwise NO.
Of the cells referenced above, only [NPS %]38 and [BG KPI]38 are linked from another sheet.
Example:
=IF(Transition38 = "Transition", IF(ISBLANK([NPS %]38), IF(ISBLANK([BG KPI]38), IF(ISBLANK([LE CM %]38), "NO", "YES"))))
Problem:
The cells [NPS %]38) and BG KPI]38) are linked fields but contain no values. I think they are causing the formula above to generate no result (the cell looks empty).
I also tried the following:
=IF(Transition38 = "Transition", IF(ISNUMBER([NPS %]38),IF(ISNUMBER([BG KPI]38), IF(ISNUMBER([LE CM %]38), "YES", "NO")))
This resulted in the same 'blank' result as described above.
However, when I removed the reference to [NPS %]38 (as below), this formula worked. .
=IF(Transition38 = "Transition", IF(ISNUMBER([BG KPI]38), IF(ISNUMBER([LE CM %]38), "YES", "NO")))
Can anyone think of a way to solve for this?
Thanks.
Bill
Comments
-
Hi William,
Tie your criteria together with the AND() function and use NOT(ISBLANK()), e.g.:
=IF(AND(Transition38 = "Transition", NOT(ISBLANK(NPS%38)), NOT(ISBLANK([BG KPI]38)), NOT(ISBLANK([LE CM %]38))), "YES", "NO")
-
Hi Shaine - Thanks for the idea. When I copied your suggestion verbatim I got an #UNPARSEABLE error. Any ideas?
Bill
-
Shaine - I found the issue with the syntax above. The column referred to in the formula was missing a space in the name. It should have been [NPS %] 38. It returns Yes/No with that fix.
However, see the below screen shot. When copied to rows where there are no values in the "NPS %", "BG KPI", or "LE CM %" columns, it returns a false positive result. Any other ideas?
Thanks in advance.Bill
-
Hi Bill,
Looks like you've got the return values flipped to be "NO", "YES" versus what's in my formula.
Try flipping those to ..."YES", "NO") instead:
=IF(AND(Transition38 = "Transition", NOT(ISBLANK([NPS %]38)), NOT(ISBLANK([BG KPI]38)), NOT(ISBLANK([LE CM %]38))), "YES", "NO")
The formula logic that I'm going with is (sorry if this sounds repetitive): if Transition contains the string "Transition" and NPS % is not blank and BG KPI is not blank and LE CM % is not blank, return the string "YES" in the cell. Otherwise, return the string "NO" in the cell.
-
That was it! Many thanks.
Bill
