Searching for duplicate numbers across multiple spreadsheets in workbook

[FinsFan]

Hi,

I've searched here as well as on the internet and have not been able to find guidance. We have a workbook we track invoices each month. Each month we start a new spreadsheet by copying the previous month, deleting what has been paid, and adding new invoices received. When adding new invoices, I'm trying to figure out how to get Excel to look at the previous spreadsheets to see if the invoice # being entered has been previously used. The format of each month is the same.

Thank you!

[#2]

@FinsFan 

Im not sure if this formula works in Excel on Mac, don’t have a Mac.

You can use maybe a formula to check if an invoice number has been previously used in another sheet the VLOOKUP function.

For example, if you have a list of invoice numbers in column A of Sheet1 and you want to check if an invoice number entered in cell A2 of Sheet2 has been previously used,

you can use the following formula in cell B2 of Sheet2: =IFERROR(VLOOKUP(A2,Sheet1!A:A,1,FALSE),"No Match"). 

This formula will return the invoice number if it is found in column A of Sheet1

and “No Match” if it is not found. 

Hope its helps!

[#3]

@FinsFan 

My Excel forum friend @NikolinoDE gave you a formula that may well do what you requested.

I'd like to raise a question about that request itself, if you don't mind.

That process you describe has all the markers of a process that has been carried over from the days of using paper ledgers to track invoices month-by-month. As such, it works, but doesn't in fact take advantage of Excel's abilities to work with a continuous database. The very fact that, as you describe it, 

Each month we start a new spreadsheet by copying the previous month, deleting what has been paid, and adding new invoices received.

sounds, right off the bat, far more labor intensive than should be necessary. But you go on,

When adding new invoices, I'm trying to figure out how to get Excel to look at the previous spreadsheets to see if the invoice # being entered has been previously used.

I have several questions about the last part of that sentenced

1. Are you responsible for creating the invoice numbers?
2. Or are you getting them from somebody else in your organization?
3. Or are the invoice numbers coming to you from outside your organization, maybe from multiple parties?
4. Or something else?

Underlying all of those: why is it incumbent on you to have to search to see if the number has been used before?

1. If you're creating them in the first place, you could just start a new series with each new month.
2. If somebody else in your own organization is creating them, that person could start a new series.
3. If they're coming from outside, you could use what they send, but have another field so as to recognize that ABC's invoice number 12345 is different from DEF's invoice number 12345.

Bottom line:  It seems to me that a possible redesign of your process might be in order. Excel really works well with single transactional databases that track over many months payment systems such as you seem to have. It's very easy from a single database to create a "dashboard" sheet that extracts (for example) invoices still unpaid, along with relevant data about them. It would not be necessary to copy, delete, check....  Just keep track on the single database invoices issued, payments made, a have summary reports on outstanding matters.  Creating that would require some work, but in the end could save a lot of work.

[#4]

Thank you@NikolinoDE working with your response now to see if that works.

[#5]

@mathetesThank you! Responses to questions:

I have several questions about the last part of that sentenced

1. Are you responsible for creating the invoice numbers? No
   
2. Or are you getting them from somebody else in your organization? No
3. Or are the invoice numbers coming to you from outside your organization, maybe from multiple parties? Yes. We receive invoices from a larger number of vendors
   
4. Or something else?

Underlying all of those: why is it incumbent on you to have to search to see if the number has been used before? I'm not the only one entering data into the workbook. 

Bottom line: It seems to me that a possible redesign of your process might be in order. This is the process that was in place when I started with the company in December. I've been trying to improve upon it since then.