Validate Cell contents based upon custom number format (determined using VLookup)

Dear all

I have a list of client reference numbers (about 15,000 in total) which are entered manually over a period of a month.
The number formats vary between clients but remain in the same format for all orders from a client.

e.g. client 1 will always be ###/##
whereas client 2 will always be #/"A"/####

The client reference number need to be correct otherwise clients delay payment (awaiting clarification)
we have this data in Excel but currently check it manually..........

We have over 1200 clients so we can't split each data set into a single sheet
all client reference numbers are currently in the same column and are sorted according to client, so we have a vertical list of over 15,000 entries grouped according to customer.

I would like to use Vlookup (if possible) to use the unique customer ID code, lookup the correct number format for the client reference number and use number format to validate the client reference number.

Two questions then

1. Does anyone know how to interrogate the contents of a cell based upon its number format and make that number format available through a Vlookup accessible list.
2. is there an automated process whereby you can display the custom number format of a cell (a bit like reveal formatting in word)

Zog

Hi Zog,

Welcome to the forum!

It will be easier for us to understand your problem if you can post the sample file.

Regards,
AM

hi there

please find attached an example of the reference numbers with the "8" location codes