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.

Blank cell only recognized as text

Brett Evans
Brett Evans ✭✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

My spreadsheet has started recognizing blank cells as text and not zeroes.

 

Every row in the RequirementNumber column is filled in with this formula 

=IF(ISBLANK([Requirement]2), "", [RequirementNumber]1 + 1)

 

The formula used to pick up "" cells in the row above it as a numeric zeros, now it is picking it up as blank text and making all of the increments in text not numeric values.

 

my numbers now look like this:

blank

1

11

111

1111

 

Instead of:

blank

1

2

3

4

 

I can manually fix this by deleting the formula in the blank cell and replacing it with a numeric zero.  The formulas adjust below and the numbering corrects itself.  If I delete the zero so the cell is blank again the numbering stays accurate.

 

Any ideas?

 

 

 

 

 

  

 

 

 

 

 

· Share on FacebookShare on Twitter

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Brett,

     

    Did this just start happening, say since the last push?

    The next release comes out on Saturday, I believe, but somethings things leak out before then.

     

    If you hadn't said it worked before, I would have said it wouldn't.

     

    I have seen some odd behavior with null and blanks -- can't say they have only been recently though. It is almost as if a blank cell that had data before (but not now) operates differently than a blank cell that has never had data.

    I think there are some programming methods where that might be case.

     

    Wouldn't it be nice to search, find & replace in formulas?

     

    Craig

    · Share on FacebookShare on Twitter
  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    I just noticed it this week.  It had worked beautifully for month to auto-number my rows.

     

    I could fix it by changing my formula to 

    =IF(ISBLANK([Requirement]2), 0, [RequirementNumber]1 + 1)

    but it would mess with my steez.

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Well, I learned what "steez" is.

     

    We have to wait for Shaine to pipe in. Maybe they will admit to something changing.


    Craig

    · Share on FacebookShare on Twitter
  • Andrew DeCounter
    Andrew DeCounter ✭✭
    edited 11/11/16

    Brett,

     

    With formulas "" is interpreted as text so we are seeing ""+1+1+1 return 111 instead of 3.

     

    I don't have a clean solution for getting a Null return on the ISBLANK() evaluation when it returns "True". You could do =IF(ISBLANK(Requirement2),Requirement1,(RequirementNumber1)+1) -- but I don't think asking a cell to always be blank is a great solution.

     

    There are a couple of work arounds you could try here to achieve the same goal. For example: ISTEXT() & LEN().

     

    I have created some examples with those formulas that could work in this case...

    https://app.smartsheet.com/b/publish?EQBCT=517f9519c74842b7b64458bab8986f5a

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories