Validate against multiple choices

[BillP]

I have searched this forum and elsewhere and can't find the answer (and it seems like such a common thing--maybe I'm not looking for the right terms). I'm using Excel 2007.

I get data in a worksheet each week, 1500 to 3500 work site records (rows), about 100 fields in each (columns). I'm using Conditional Formatting to highlight errors (I need to make judgements about the problems before fixing them). And in many fields I check the values in the worksheet against the results of a VLookUp. That all works fine as long as there is only one option available for a field.

My problem is that I need to check some fields that could have multiple correct answers. I can check against a work site list, but if a site can provide one of 4 possible services, how do I check that the service listed in the weekly worksheet is legitimate (based on a table as below), and not a data entry/scanner error?

Site name---Service---Service---Service---Service
Site AAAA---156785---164878---23847-----1648
Site NNNN---256858---256485---254-------23874
(sorry for making the pseudo columns like that)

VLookUp doesn't work for this. Can you point me to a solution that will work in Conditional Formatting?

 

[jc.021286]

Hey there,

What you are trying to do can pseudo be done with named ranges and the indirect function.

If we have a named range called "AAAA" it will match the contents of Cell A2 your site "AAAA" it is important that they be exact.

Now when we use the indirect function on A2 it will look for "AAAA" which would then return our named range automatically. This can then be applied how you wish.

This sort of indirect functions are real nice for getting down to a root list. Thus, if I create validated lists of Senior management, middle management and floor people. Using validated lists, I could use the above methodology to avoid allocating a floor personnel to the wrong manager or wrong manager to senior management. You would tweak this to your conditional formatting to achieve your desired results.

I hope this helps,
jc

 

[BillP]

I might not have been clear earlier; what I posted in the grid (below) was the valid data against which cells in the worksheet will be checked.

So, for example, in the Weekly Worksheet, I need the "service" cell for site NNNN to check this list and see if the number in the worksheet matches one of the numbers in the row next to Site NNNN (Row 3 here) and flag it with a color if it does not match.

The numbers are not unique to each site. There are about 850 sites that perform these services and the records we receive each week represent one report for each service rendered (so it may be that a site submits many, many reports each week).

Is the named range B1:E850? (It's not a named range for each site, is it?). If I name the range B1:E850 as Service_Codes, how do I use Indirect with it?

I really apologize if these are basic questions; I just haven't found anything in Excel Help or other sites that seems to relate directly to what I'm trying to do. I'm normally a fairly competent user of Excel.

Weekly Worksheet to be validated:
HAGD--blah--blah--blah--blah--blah--258456--blah--blah, etc.
NNNN--blah--blah--blah--blah--blah--987654--blah--blah, etc. <-wrong #
NNNN--blah--blah--blah--blah--blah--258456--blah--blah, etc.
AAAA--blah--blah--blah--blah--blah--56789---blah--blah, etc. <-wrong #


Master list of Valid Codes:
----- Column A --- Col. B --- Col. C --- Col. D --- Col. E
Row1 Site name---Service---Service---Service---Service
Row2 Site AAAA---156785---164878---23847-----1648
Row3 Site NNNN---256858---256485---254-------23874
Row4 Site UAGD---256485---258456---56458-----521
(sorry for making the pseudo columns like that)

 

[jc.021286]

Okay,
here is the magic and we will make it all disappear,
In a new tab, we'll call it "Ref", this will be our grand reference tab. (you could easily refer to your "Master list of Valid Codes" if that is how you call it)

as you have 850 sites, I would prefer to list these down rows and not across columns.

In Ref you would have

SITE   Service1   Service2   Service3   Service4
AAAA   12345        23456      45678         78
BBAB    23456          72           13         12789
.....
850 records itemized
....

If SITE is in Column A and services are B:E or more

Your first named range is SITE
This will Be from Ref!$A$1:$A$851

Now you would create other named ranges,
Name Range
AAAA Ref!$B$2:$E$2
BBBB Ref!$B$3:$E$3
... etc

with these named ranges we go to your main page. for weekly validations.
Your first column for site names set this as data validation > List
Formula for the list will be =SITE
^^ our named range for all the sites, now only the sites can be listed here, this prevents errors in entry.

You will do the same for your 2nd table, the 4 columns for services will have data validated lists =INDIRECT($A2)
Do that for the first one, fill right than fill down.

Now you will have drop downs that will ONLY allow valid numbers, if the number is not listed, than those that are inputting cannot input an invalid number.


Try this for the first 3 items and get it working to see the results, the data list validation can also be used inside your conditional formating.

Another methodology, using this settup, would be the sumproduct formula,
Conditional formatting =Sumproduct(--(Indirect($A2)=B2)=0

The sumproduct will search if the contents of B2 (the cell you are putting this conditional formatting on is contained within the named range from A2, this uses the "--" to convert results to ones and zeros, if none of the cells are equal to the named range you get 0, if true, your conditional formatting.

Sorry long winded, get back to me if you run into problems.
jc

 

[BillP]

I can't use Data Validation in the normal sense of making drop-downs, unfortunately, and I apologize if I steered you down the wrong path.

The worksheet that I receive is the result of scanning thousands of paper forms submitted by sites with approx. 100 handwritten response fields, check boxes, and filled-in bubbles (like SAT tests) in each. The forms are scanned, OCR'd and checked preliminarily by our staff (But they can't check against valid number lists, etc., they only proof against the paper form, so the errors could come in the field, the OCR process, or the staff's proofing.) that's where I come in--setting up a process to validate all the data against accurate lists of available codes.

 

[jc.021286]

Okay,

Should still work, we just run the issue that "AAAA" would not match to "AAAA-" or "AAAA "

using the named range with indirect formula should still work for yourself.

without using the conditional formatting

  A     B    C     D     E     F      G      H     I    J
HAGD--blah--blah--blah--blah--blah--258456--blah--blah, =sumproduct(--(indirect($A2)=G2)>0,"","<<< ERROR HERE")

With your master validated list, this will place into column J nothing if correct of a big "<<<< ERROR HERE"

Your conditional formatting would run off of the same principle if you rather go that route.

This will do what you specified 100%. Only issues are as I specified, if the name in column A is not an exact match with a named range.

Let me know if you wish for any more help, I have tested this out on a splash sheet and have it working. So the methodology is good.
jc

 

[gecs]

The Conditional Formatting based on a formula ("Use a formula to...") applies the different format when the result of that formula returns TRUE. So, if you have the "Master List of Valid Codes", first name the data (exclude the headers) "Valid_Codes" - you may use an OFFSET based formula to associate that name to a dynamic range for both rows and columns (just an idea, it's not mandatory).

Then, in B2 you may use the following formula in the Conditional Formatting dialog:
=ISNA(MATCH(B2,INDEX(Valid_Codes,MATCH(A2(INDEX(Valid_Codes,,1),0),),0))

The above formula will return TRUE if the code in B2 will not be found in the row of the "Master List of Valid Codes" where the value in A2 is found in column 1.