Link two different columns that have job numbers

Michael F

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

Paul Newcome

You will need to create a 3rd column that contains a formula along the lines of...

=IF([Previous MET #]@row <> "", [Previous MET #]@row, [Job #]@row)

Michael F

Thanks @Paul Newcome

This works is we focus on that new third column as the source of truth (i.e it displays either the new or old MET number) it still creates a new MET number in the 'Job # column'. Is there a way that when we have an existing MET number ('Previous MET #' column) a new job number is not created (no new number in 'Job #' column)? I tried looking at the submission form though this didn't unearth an insights. It seems that if I have an 'Auto-Number/System' column you can't do any formulas over the top of this?

Sorry if I'm reading all this wrong, I have basically zero coding experience!

Thanks so much for your help.

Cheers,

Michael

Paul Newcome

You are correct that auto-number columns cannot have a formula put over top of it. You can hide the auto-number column and just show the manual entry for the existing and just instruct users to leave blank if a new one is needed or fill in if they have an existing.

Michael F

Cheers - thanks for your help @Paul Newcome

Paul Newcome

Happy to help. 👍️