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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives