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.

Generate a word in a column if an item is added to a list

Lucas Knepper
Lucas Knepper ✭
edited 12/09/19 in Archived 2017 Posts

I am trying to build a formula so when a number is entered into the Lot Number column, the cell in the next column over (Lot Status) will automatically generate the word "Available". I have tried the following formula with no success: 

 

=IF([Lot Number]:[Lot Number] = 1, [Lot Status]:[Lot Status] = "Available")

 

Please note, I just put =1 after lot number because I need it to pick up when a number is typed into that cell. 

 

Thanks!

· Share on FacebookShare on Twitter

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭

    Hi Lucas,

     

    I assume that you mean, "when I put any value in a cell in the [Lot Number] column then I want the corresponding cell in the [Lot Status] column to show the word "Available" and otherwise to show blank (actually null)".

     

    If this is the case, then one way to achieve this is to put the following formula in the first row of the [Lot Status] column and then copy the formula all the way down the sheet to the end of the data:

     

    =IF([Lot Number]1 = "", "", "Available").

     

    I hope that this assists.

     

    Cheers,

     

    Rob.

     

    PS. I would have thought that a lot was available if the number was absent, but I don't know your actual situation. If it is that way, then simply reverse the "" and "Available" in the IF expression.

    · Share on FacebookShare on Twitter
  • Lucas Knepper
    Lucas Knepper ✭

    Rob, 

     

    That worked! I am trying to get it to apply to the entire column so that no matter how many Lot Numbers are entered, it will always generate available. I used your formula but tweaked it to encompass the entire column but it didn't work (see below). Any suggestions?

     

    =IF([Lot Number]:[Lot Number] = "", "", "Available")

    · Share on FacebookShare on Twitter
  • Rob Hagan
    Rob Hagan ✭✭✭

    Hi Lucas,

     

    If we were working in an environment other that a "spreadsheet" then you may have a chance of using a technique like the one that your formula =IF([Lot Number]:[Lot Number] = "", "", "Available") implies. Sadly, we are not.

     

    A cell in a "spreadsheet" contains a value or an expression (i.e. a formula). The evaluation of that value or expression provides what you see as the "value" of that cell. Unless there is a macro language (e.g. Visual Basic (VBA) in Excel), you don't have the ability to "poke" a value into a cell from outside the cell.

     

    So, you are restricted to the approach that worked above (i.e. having a whole column of cells each with the "same" formula).

     

    But all is not lost. To assist you, SmartSheet has the ability to propogate formulas downwards when you add fresh rows (provided that the two rows above contain the same formula).

     

    Cheers,

     

    Rob.

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

Categories