Create a formula that auto numbers

Marcy Morris

Hi I'm in need of a formula to execute the following:

I have a Project tracker that has auto numbering that I now need to change. When a new Project is added to the worksheet containing a Project ID with the letters "P" or "HR" I need to retain that Project ID instead of the next auto number Project ID. When a project is added to the worksheet that does not contain a Project ID then I need the auto numbering to continue from the last auto number. I'm in dire need of a solution.

Nick Korna

Hi @Marcy Morris

The following formula would work (in Output column):

=IF(OR(CONTAINS("P", Project@row), CONTAINS("HR", Project@row)), Project@row, Autonumber@row)

This assumes that your projects with "P" or "HR" don't have longer chains containing these characters.

You can set the Autonumber column to start from a later value if you're pasting in data that already exists.

For visibility you could hide the Autonumber to have only the Output column showing.

Hope this helps - if you've any questions just ask.

Nick Korna

Hi @Marcy Morris

The following formula would work (in Output column):

=IF(OR(CONTAINS("P", Project@row), CONTAINS("HR", Project@row)), Project@row, Autonumber@row)

This assumes that your projects with "P" or "HR" don't have longer chains containing these characters.

You can set the Autonumber column to start from a later value if you're pasting in data that already exists.

For visibility you could hide the Autonumber to have only the Output column showing.

Hope this helps - if you've any questions just ask.

Marcy Morris

Hi Nick

Thanks for your speedy response I was so thrilled it worked that I completely forgot to thank you.

Kind regards

Marcy