Forum Discussion
Create Invoice Number based on 2 columns and a counter
The time column makes it work exactly as I am looking for....assuming the user remembers to enter the current time into the sheet. Hmmmm....just couldn't rely on that. So...
I found a method that will enter the current time into the sheet automatically using the NOW() function and circular reference. https://trumpexcel.com/date-timestamp-excel/#Circular-References-Trick-to-Automatically-Insert-Date-and-Timestamp-in-Excel
Formula in TIME column:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Now, using this formula to set the time in a hidden/locked cell, I can use your formula (adjusted) to get the exact outcome I am looking for.
Final formula using the new (auto populating) TIME column:
=IF([@[Truck '#]]<>"", [@[Truck '#]]&"-"&TEXT([@[SERVICE
DATE]],"YYMMDD")&"-"&MATCH([@TIME],SORT(FILTER([TIME],([Truck '#]&[SERVICE
DATE])=([@[Truck '#]]&[@[SERVICE
DATE]])))),"")As an added bonus, these formulas also work on the web version, so the user can enter the data from either the web browser or from within the Excel app.
Thx mtarler for all your help
Next, do you have Excel 365 or some older version of Excel (i.e. we need to know which functions are available)
basically I am thinking of a formula like (i.e. I just wrote it here without testing it because it still depends on your answers):
=[@Truck]&"-"&Text([@Date],"YYMMDD")&"-"&Match([@[Load #]],Sort(Filter([Load #],([Truck]&[Date])=([@Truck]&[@Date])))
a) is it persistent
- Yes...the Load # for that trip (truck/date combination) does not change once set
b) is it unique?
- No....The Load # is set by multiple different vendors, so theoretically Vendor A may have the same Load # as Vendor B.
The truck/date/load # combination is unique though. The boss wants the "counter" instead of the load # added to the end of the invoice as the load # can be any where from 3 (smallest so far) to 11 (length is unlimited) characters (alpha/numeric) long.
- ok so did you try my formula?
- I just tested it.....looks like it is stable (resorted on different columns and it maintained.) Far closer than I got.
When I add an additional trip (load id) with the same truck/date combination, the "counter" from the first load that was entered is incremented and the new load is appended with a '1.' This is close, but I need the new load to be appended with the '2' or '3' (what ever the current count is) and the load that is already in the list to maintain their "counter."- It will 'lock in' after all the loads for that day are finished but yeah if new loads are added with different load names it will likely change the numbers.
The only suggestion I have for you is to add a entry time. Then you can do that same filter/sort/match but use entry time and then it is guaranteed to be in order of when you enter it.