Aug 02 2020 12:17 AM
Good day,
Please assist me with a formulae to auto increment the Invoice number after looking up the previous value for the specific client.
See attached file.
Thanks.
Aug 02 2020 12:26 AM
How about this?
In B25
=IF(AND(ISNUMBER(B24),D25=D24),B24,INDEX($B$3:$B$20,MATCH(D25,$D$3:$D$20,0))+1)
and copy it down.
Aug 02 2020 01:19 AM
Aug 02 2020 01:53 AM
Thank you Subodh.
However, I expected the Invoice nr. to auto-increment by 1 as long it is a new shipment to a specific customer. May be I did not explain myself properly, but the next shipment for let's say Al Hilal, should have Invoice nr. 33, then 34, then 35. The Delivery note number also increments per delivery and is not bound to a customer. Same with the other customers.
Aug 02 2020 02:52 AM
Okay try this and see if it returns the desired output.
In B25
=IF(AND(C25=C24,D25=D24),B24,IF(ISNUMBER(MATCH(D25,INDEX(D$24:D24,),0)),MAX(IFERROR(INDEX((D$24:D24=D25)*(B$24:B24),),0))+1,INDEX($B$3:$B$20,MATCH(D25,$D$3:$D$20,0))+1))
and copy it down.
Aug 02 2020 03:25 AM
@A_SIRAT , as a variation to @Subodh_Tiwari_sktneer 's solution (if you have the latest Dynamic Array version of Excel):
=MAX(FILTER($B$3:$B24,($D$3:$D24=D25)*($C$3:$C24<C25))) + 1