Formula to auto populate a number based off cell input

David Budnick

I'm looking to see if there's a formula or other means to have Smartsheet auto populate a quote number and duplicate that number if the same name is added later on.

I am working on building a quotes log where we input a project name in Job Name which then auto fills to the Job Name Master. Next to the Job Name Master Column there is are sequential numbers to use as quote identifiers. I've tried a few different formulas without much luck.

The process would be we enter project name ABC 123 in Job Name cell #1. That name auto fills in Job Name Master cell #1 which is next to Quote Number Master cell 1 (in this case the number 16000). What can we do to trigger "16000" to populate in Quote Number cell #1? Then if DEF 456 is entered in Job Name cell 2 have number 16001 auto populate in Quote Number Cell 2. The last step is if ABC 123 is entered in Job Name cell 3, have "16000" auto populate in Quote Number cell 3 because it's a duplicate. Then the next number as a unique identifier would be 16002. So forth and so on down the list.  

Alejandra

Hi David,

The formula below should give you what you're looking for. You can place this formula in the Quote Number column:

=VLOOKUP([Job Name]@row, [Job Name]:[Quote Number Master], 2, false)

If needed, more information on VLOOKUP can be found in our help article: https://help.smartsheet.com/function/vlookup

David Budnick

thank you Alejandra