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

Lucas Knepper

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!

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.

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")

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.