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

Romano el Polako
Romano el Polako ✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    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:

    image.png

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

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!