Searching for duplicate numbers across multiple spreadsheets in workbook

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.