how to prevent duplicate invoice numbers on separate workbooks?

[aoifebrown22]

hi, i need help with a duplicate invoice problem across multiple work books. i'd really appreciate any help or ideas with this if at all possible.


I have 20 separate workbooks, all the same format within. received invoices are entered line by line on the sheets. unfortunately this means that an invoice with five items listed will result in an invoice number being entered on five lines on the sheet this will cause a secondary problem but they can be entered with a letter at the invoice numbers end to prevent it. the workbooks run on a monthly basis so when the month ends new ones are started for the separate departments. the problem I'm running into is that we can't stop duplicate invoices being entered in separate months. I've tried vba but can only get it to work for one work book, I've tried macros but the vast amount of separate workbooks is causing problems. I also need the solution to be adjustable for subsequent books or sheets to be added.


is there an easy solution that im missing or is this task just to massive. any and all advice would be greatly appreciated

 

[Sajan]

Hi,

Have you considered having a single master source for all of your invoice numbers? For example, you could have a worksheet that lists all the invoices, and refer to it from every other workbook (perhaps as a dropdown list).


That will help ensure that you have control over what invoice numbers get added to the "child" workbooks.


Cheers,

Sajan.

 

[NARAYANK991]

Hi Sajan ,


I think the problem is that the application is being done using Excel !


This is an ideal candidate for Access.


The problem mentioned by the OP is that the same invoice number can be entered in different workbooks , since at present there is no verification that an entered invoice number has not already been posted earlier in some other workbook. Even a master list of invoice numbers and drop-downs will not help to eliminate duplicate data entry. They can help to avoid erroneous invoice numbers.


Narayan