IF formula to validate if cell is number and is not blank

Romano el Polako

Hey Smart-Heads,

I would like to create a validation formula for a cell with manual inputs.

ASIN Position on SAD - column which initially is blank, later updated manually by the user

ASIN Position on SAD - Number Check - column which checks the inputs provided. Values and scenarios:

YES = 1) ASIN Position on SAD is not blank and 2) ASIN Position on SAD is number

NO = 1) ASIN Position on SAD is not blank and 2) ASIN Position on SAD is NOT number

BLANK = 1) ASIN Position on SAD is blank

So far I have formula for isnumber:

=IF(ISNUMBER([ASIN Position on SAD]@row), "YES", "NO")

Tried to merge into one (without blank scenario):

=IF(AND(NOT(ISBLANK([ASIN Position on SAD]@row)), ISNUMBER([ASIN Position on SAD]@row), "YES", "NO"))

Thanks in advance for great as always support!

Romano

Nick Korna

Hi @Romano el Polako,

You would want a nested IF formula for this:

=IF(ISNUMBER([ASIN Position on SAD]@row), "Yes", IF([ASIN Position on SAD]@row = "", "Blank", "No"))

Sample data:

Hope this helps, but if I've misunderstood anything or you have any problems/questions then just post!

Nick Korna

Hi @Romano el Polako,

You would want a nested IF formula for this:

=IF(ISNUMBER([ASIN Position on SAD]@row), "Yes", IF([ASIN Position on SAD]@row = "", "Blank", "No"))

Sample data:

Hope this helps, but if I've misunderstood anything or you have any problems/questions then just post!

Romano el Polako

@Update

I have actually found a walk-around solution, by creating another column, see:

ASIN Position on SAD - Number Check

=IF(ISNUMBER([ASIN Position on SAD]@row), "YES", "NO")

ASIN Position on SAD - Blank Check

=IF(ISBLANK([ASIN Position on SAD]@row), "BLANK", "NOT BLANK")

ASIN Position - Blank and Number Check

=IF([ASIN Position on SAD - Blank Check]@row = "BLANK", "OK", IF([ASIN Position on SAD - Number Check]@row = "YES", "OK", "NO OK"))

based on the last column I will create Conditional Formatting.

Nevertheless, if you see a way to simplify it, I am listening to you, thanks!

Romano

Romano el Polako

https://community.smartsheet.com/discussion/comment/404582#Comment_404582

This is actually great, thank you very much!

Nick Korna

No problem at all, happy to have helped. 🙂