Forum Discussion

Copper Contributor

Aug 04, 2021

Solved

Create Invoice Number based on 2 columns and a counter

Shared sheet through SharePoint. I have created an invoice tracking table that has about 40 columns. The formula I am currently using to create the invoice number concatenates the TRUCK#, DATE, a...

excel

Excel for web

Formulas and Functions

office 365

DarrMik
Aug 05, 2021
mtarler
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

mtarler

Silver Contributor

Aug 05, 2021

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.

DarrMik

Copper Contributor

Aug 05, 2021

mtarler

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