macro for getting number of invoices every month

[Alina_T]

Hello,

Please help me with an issue: I will receive a worksheet with 2,000 suppliers and these suppliers could have 1 or 5 clients. I have to make invoices for these suppliers in stand of them and to give them a unique series of invoice and a number. I will start with 1 for the first supplier invoice, but if he has 3 clients, macro should complete until 4 (based on clients an supplier). Next month macro should know the last number of invoice from previous month and start with it +1 and continue based on number of clients.
Please help me, I've tried in excel with formulas, but I don't know how to increment after I get the last number.
Thank you in advance!

 

[vletm]

Alina_T
Please, use same terms with Your writing and Your file. I guess/wonder 'suppliers' is ...
Here one formula sample ...

 

[Alina_T]

Sorry, you are right. Thank you for the solution, but it isn't what I need, because I wasn't very specific. My bad.
Please look into this excel, I hope it's more clear now. I need to put the numbers of invoice from April automatic, formula or macro, based of numbers and series from march and to increment where I have more clients:
One example (but not from excel, I realized now):
Supplier A - has in March 2 clients with no inv 13th and 14th
Supplier A has in April 5 clients and I have to put automatically no 15th,16th,17th - macro should identify the last number from March and increment only for that supplier based on no of clients.
If supplier A will not have any client in May, but in June will have 2 client - it should identify that the last number was 17th and in June it will start from 18th and increment for the next with 1 - the numbers from June will be 17th, 18th, 19th.
Thank you very much!

 

[Alina_T]

I think this will work, but surely would help more a macro because in formula I have to change every time and could happen more errors.
=MAXIFS(no inv from precedent month)+COUNTIFS(appearance of supplier in current month)

 

[vletm]

Alina_T
That works,
but why there should be any Monthly-sheets?
If 'Monthly-sheets' then those have to have useful name.

 

[Alina_T]

I don't think I get the point - I'm not very strong in excel, I'm still learning

 

[vletm]

Alina_T
Other sample.
Try to sit and think, could You use eg this kind of sheet?
Left side is 'date' to mark - which date ...
I also took that 'column which has text about Month' away.
... as well as I added 'needed' rows before March (those do not have all correct data - of course).

 

[S. Das]

Alina, please check whether my sheet is working as per your need or not.
Here, instead of making month by month sheets, you have to maintain two sheets only one is for Previous months and another is for Current month.

Note: "Month" Column should be sorted as Oldest to Newest for the Previous sheet.

 

[Alina_T]

Alina_T
Other sample.
Try to sit and think, could You use eg this kind of sheet?
Left side is 'date' to mark - which date ...
I also took that 'column which has text about Month' away.
... as well as I added 'needed' rows before March (those do not have all correct data - of course).

Thank you! I understand and could you be right. Only that I have to use mail merge based on this data and I have to see if I can select only a month for making these invoices.
Thanks a lot for all the support!

 

[Alina_T]

Alina, please check whether my sheet is working as per your need or not.
Here, instead of making month by month sheets, you have to maintain two sheets only one is for Previous months and another is for Current month.

Note: "Month" Column should be sorted as Oldest to Newest for the Previous sheet.

Thank you! I will try the solution and give you a feedback! Thanks a lot!

 

[vletm]

Alina_T
You can filter almost any period from that datas as You want.