Forum Discussion
Table for invoice number
Hello, i have a simple question to ask. I have a file in excel that i use to print the invoice for my work and also to store the data of the client and the link to the previous invoice.
In one of the table i wanted to have the invoice progressive number and in order to do so i used the concatenate function and i simply put the progressive of the month and the year.
This was okay untill now because i made one invoice at month, but now i need to make three of them each month. Is there a more elegant solution that automatically generate the progressive number as shown without having to add the number manually? It also need to change the year after the last invoice of the current year.
I've attached a screenshot of the table right now. The formula that i'm using is the following (i take as example the cell B40)
=CONCATENATE(E40;".";$F$40)
Thank you in advance.
For example in row 1:
=TEXT(MOD(QUOTIENT(ROW()-1,3),12)+1,"00")&".""IENT(ROW()-1,36)+18&"."&MOD(ROW()-1,3)+1
And then fill down as far as you want. This version will start with 01.18.1 for 2018. If you want to start in a different year, change 18 in the formula.
7 Replies
As a 365 dynamic array
= LET( k, SEQUENCE(108,1,0), m, QUOTIENT(k,3), date, EDATE(start,m), seq, 1+MOD(k,3), TEXT(date,"mm.yy-")&seq )seems to work.
PeterBartholomew1 yeah, i think you'll need to guide me to use that ^^"
I'm quite skilled in the basics of excel with formulas and so on, but i don't know how to use VBA and other things
I can see why you are confused, but the formula is just a standard worksheet formula in Excel 365!
LET is a new worksheet function that uses an alternating pattern of parameters to define names (variables) and the formulas that they refer to. SEQUENCE is another function that I have used to generate an index column but in legacy Excel this would be done with ROW().
The key question is do you have access to Excel 365? It is very different from anything that has gone before.
