Link two different columns that have job numbers

Michael F
Michael F ✭✭
edited 03/03/22 in Formulas and Functions

Hi all, hope everyone is well.

Hoping for some advice and insights into developing a formula before we move over our projects to Smartsheet

See image example attached/below:

  • - I need to have the ‘Job #’ column only generate a new number (MET number, is our existing job ticket number) when the ‘Previous MET #’ column does not contain data.
  • - Essentially we reuse the job numbers for specific projects as we have loads of little changes to brochures and other materials that just require the artwork in the folder to be updated though a new folder is not required (hence a new job number is not required)
  • - Hence only new jobs will get a new ‘MET number’ and old jobs will reuse their existing ‘MET number’.
  • - Any idea how to write a formula for this? Would be something along the lines of;
  • - If ‘Previous MET #’ column doesn’t contain data then create a new MET number in ‘Job #’ column. If ‘Previous MET #’ contains data then copy this into ‘Job #’ column.
  • - The new MET number would need to start from a predefined number (i.e MET4400) and then as each new job number is created sequentially rise by 1 (i.e MET4401 then MET4402 etc etc)

FYI in the attached image:

  • - Job # column = is set to ‘auto-number system’ and has been structured as METXXXX (we would like to start at MET4400)
  • - Previous MET # (essentially the job number from our previous project management system) = set to text/number and is inputted when the user submits a brief

If anyone has any ideas, I’d be very appreciative!

Thank you.

Cheers,

Michael


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!