Autopopulating a job number

Angela Hill CC

I have set up a sheet that will house our jobs. It has been requested that each job have its own row, nestled under the customer's name. Our job numbers are derived by using the customer number, year, and job number for the year. For instance, the first job of 2024 for Customer A with a customer number of 1000 would have a job number of 1000-24001, the second job for them would be 1000-24002. I have it successfully working in a row as you can see in the image below. However, this row has to be copied and pasted to the next row. I'd like for it to be autogenerated. Below I explain how I have this set up. It is likely convoluted, but I am doing my best without any real training!

job numbers.jpg

Both the Customer Name and Customer Number are currently selected from a linked sheet, by right clicking, selecting Link from Cell in Other Sheet, choosing the Customers Sheet I've created, and selecting the Customer Name, then the Customer number in the corresponding cells.

I've created a year column function using the function YEAR(TODAY ()) - 2000. The user will manually complete Job Column (001, 002, 003, etc). To create the job number, I am using the join formula to create a column called Start of Job Number which Joins the Customer Number and Year Columns together separating them with a -. That, along with the Job Column that was filled in by the user to create a Job Number column using the simple formula of [Start of Job Number]@row + Job@row. Confusing, but it is working to get the desired job number of Customer Number - Year Job Number.

I'm confident there is a cleaner way to do this, but I've literally been at this for 2 days, so please be kind! Right now, my main need is for the user not to have to copy this row manually. Any suggestions are appreciated.

Paul Newcome

Yes. You would first need to reset your auto-number column to have no formatting, no leading zeros, etc.. Then your formula would look like this:

="1386-" + RIGHT(YEAR([Start Date]@row), 2) + RIGHT("00" + COUNTIFS([Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), [Row ID]:[Row ID], @cell <= [Row ID]@row), 3)

Paul Newcome

@Angela Hill CC Glad you were able to get it sorted on your own!

Paul Newcome

This should work for you and remove the need for helper columns and make it so you only have to select the Customer Name the one time on the parent row (as long as the new job is indented under the customer parent row, it will work).

First insert a system generated Created (Date) type column (called "Created" in this example).

Next insert a text/number column (called "Customer" in this example) with this column formula:

=IF(COUNT(CHILDREN([Customer List]@row)) = 0, PARENT([Customer List]@row))

Then, following proper steps to create cross sheet references, use this formula in the [Job Number] column:

=IF(COUNT(CHILDREN(Customer@row)) = 0, INDEX({Cell Link Sheet Job Number Column}, MATCH(Customer@row, {Cell Link Sheet Customer Name Column}, 0)) + "-" + RIGHT(YEAR(Created@row), 2) + RIGHT("00" + COUNTIFS(Customer:Customer, @cell = Customer@row, Created:Created, AND(@cell <= Created@row, IFERROR(YEAR(@cell), 0) = YEAR(Created@row))), 3))

Angela Hill CC

Thanks @Paul Newcome I'll give this a try today and let you know if it works for me.

Angela Hill CC

Well @Paul Newcome it sort of works. The first formula populates the child rows perfectly. However, the [Job Number] column produces an #unparseable error when I copy the final formula to it. I haven't had any time to investigate to see what I am missing, though. Any suggestions you can offer would be great!

Screenshot 2024-07-31 124939.png

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Angela Hill CC

@Paul Newcome

Paul Newcome

It is almost as if it isn't registering column names. What happens if you highlight one of the Created@row references and then clicked on a cell in the Created column? Same for one of the Customer@row references?

Angela Hill CC

It is as though they aren't connected to anything in the sheet, so like you said, not recognizing the column names. I'll try to recreate the sheet making sure everything is named as in the formula, or edit the formula. It's going to be a bit before I can get back to it. I'll keep you posted. Thanks for your quick response!

Angela Hill CC

Hello @Paul Newcome, I'm finally getting back to this. I've gone another direction and need some more guidance, if you can help. I created an individual customer sheet, and thanks to many great posts, derived this formula

=[CUSTOMER NUMBER]@row + "-" + RIGHT(YEAR([START DATE]@row), 2)

As you can see below, it picks up my Customer Number, which is added by the simple formula of =1368. It adds the dash, then adds the 2 digit year which is picked up from Start Date. I now need to add the numbers 001, 002, 003 etc. to each following row. I tried adding "00" +1, but this gave me the same result of 1368-24001 in each row. I also realized that 00 would stop applying when I got to job 10. Any suggestions on what to add to the formula to make the rows populate 001, 002, etc as new rows are created?

Any help is appreciated!

Angi

Angela Hill CC

Think I got it. I just added an auto number column and added that column to the end of the formula! Now another question arises…can I make the Job Number reset to 001 when the year changes to the next year?

Paul Newcome

Yes. You would first need to reset your auto-number column to have no formatting, no leading zeros, etc.. Then your formula would look like this:

="1386-" + RIGHT(YEAR([Start Date]@row), 2) + RIGHT("00" + COUNTIFS([Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = YEAR([Start Date]@row), [Row ID]:[Row ID], @cell <= [Row ID]@row), 3)

Angela Hill CC

@Paul Newcome You are the best! That worked perfectly. Thank you so much!

Paul Newcome

Happy to help. 👍️

Angela Hill CC

@Paul Newcome I've got another question for you. If I want this formula to refer to the data in my Customer Number column instead of physically changing the customer number in the formula each time, is that possible?

This is the formula you gave me, which works, but I don't want to have to change the characters at the beginning of the formula for each customer sheet.

I would like for the formula to pick up the data from column Customer Number so that the formula doesn't have to be adjusted for each new customer sheet. I tried a few varieties of this formula, but they all tell me I have syntax errors.

Hope this makes sense! Thanks for any help you can offer.

Angi

Angela Hill CC

@Paul Newcome I got it on my own! Too many parentheses. Thanks!

Paul Newcome

@Angela Hill CC Glad you were able to get it sorted on your own!