Per Catalin's request please see the attached sample data. Please note, almost every last column in the original spreadsheet is based on formulas. At least everything from column BM on.
I am working with invoice numbers entered manually into a large worksheet that is vital to tracking cost and sales margins. Each invoice number may have multiple line items if there is more than 1 item on the invoice. Sometimes during the course of the year I fail to input an invoice number because of the multiple places I have to put that information and the sheer volume of invoices on some days and don’t realize it until the invoice is paid. The list is in a table format so the range is named. The list grows daily. Each year I would have a new starting invoice number.
My invoice numbers are in column O entitled Inv#. I need it to either highlight the two numbers around which the invoice number is missing or list it on a separate sheet in the workbook.
Hi Trish
Welcome to the forum.
Philip Treacy had written about this in a post. You can refer the link below to get details.
https://www.myonlinetraininghub.com/find-missing-numbers-really-fast
Hope this helps.
Sunny
Hi Trish
Please refer to the attchment, in the worksheet named "missing num", column A is a helper column which begin with the smallest invo number. If the number in column A exist in column O of the first worksheet the formula in column B returns a value, else the formula returns "missing". Filter column B you will find all missing invo number.
This could be done without a helper column with an array formula, but i think a helper column is a more easier way.
hope this could solve your problem.
Clark
Thanks, Sunny.
The code works perfectly and doesn't add any more formulas to my heavily formulated workbook. The helper column version was good as well. But as I said, I already have so many formulas in this workbook that if I am not careful it freezes up my system. So, being able to put a code into my Personal Macro Workbook reduces the potential formulas and allows me to share the workbook without burdening my colleagues with a macro-enabled workbook.
Trish
Hi Trish
Thanks for your feedback. Glad to know you have resolved your issue.
Sunny
