Jul 30 2021 01:41 AM
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.
Jul 30 2021 03:20 AM
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.
Jul 30 2021 03:22 AM
@Peter Bartholomew 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
Jul 30 2021 04:28 AM - edited Jul 30 2021 04:33 AM
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.
Aug 02 2021 06:27 AM
Aug 02 2021 06:42 AM
SolutionFor 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.
Aug 02 2021 07:50 AM
Aug 02 2021 08:04 AM
Glad that you found a solution. The Italian version of the formula that I posted would be
=TESTO(RESTO(QUOZIENTE(RIF.RIGA()-1;3);12)+1;"00")&"."&QUOZIENTE(RIF.RIGA()-1;36)+18&"."&RESTO(RIF.RIGA()-1;3)+1
Aug 02 2021 06:42 AM
SolutionFor 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.