data Validation not fully working

[EliseFreedman]

Hi There

I have a spreadsheet which contains dependant drop down boxes. The contents of the drop down boxes are all dependant on what the user has selected in the previous drop down, for example if they have selected AT WORK then the next column will have options including TURN OFF PC AT NIGHT, USE VIDEO CONFERENCING etc. Similarly if they chose AT HOME in the first drop down, the next column will have ACTIVELY RECYCLE, TURN THERMOSTAT DOWN etc. The formula for the 2nd column (F22) is as follows =INDIRECT(VLOOKUP(D22,PartsLookup,5,0)) where the partslookup list is a named range on another worksheet containing all the possible values.

My problem is that the validation does not appear to be working for the 2nd column. Whilst the user can select from the values in the drop downs and the correct values appear, there is nothing to stop them ignoring what is there and typing their own text. I have ticked the box to say show error alert if invalid data is entered but it still lets the user type in their own data. The validation works OK for the first column. The only difference I can see is in my formula within the data, validation. The first column simply refers to a named range.

Can anyone tell me how to get validation to work when using the indirect formula.

 

[SkipVought]

What does
[tt]
(VLOOKUP(D22,PartsLookup,5,0)
[/tt]
return for the given D22 value?

Is there a Named Range for THAT value?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EliseFreedman]

It returns the expected value. my issue is that if users desire, excel is letting them type in their own value rather than select from the drop down.

The named range for the D22 value is PartsList which is stored on the same worksheet as the other named range

 

[SkipVought]

I ran a test an get a DV on my second box

Is your FORMULA in the DV > LIST option?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EliseFreedman]

Yes it is

 

[SkipVought]

I set up 2 DV drop downs, returning a Range Name from the first DD and using the reference to that first DD in =INDIRECT(ref)

So maybe what YOU need is something like this...
[tt]
=INDIRECT(D22)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Hi Elise, you say:

The named range for the D22 value is PartsList

... Skip asked: what does VLOOKUP(D22,PartsLookup,5,0) return.

Are you saying that the VLOOKUP returns the value "PartsList"?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Deniall]

A few weeks ago a colleague asked me how to set up something like you describe: data validation where the list of allowable entries in a secondary column depended upon the value (also subject to validation) that was entered into a primary column. I set up the attached spreadsheet to demonstrate it for him. This enforces validation on both the primary and the secondary columns, meaning that it does not have the weakness you seem to be encountering.

It contains its own explanations of the steps required. It was developed using Excel-2010, but I can see no reason why it should not run fine under earlier versions.

(This is my first attempt at a file upload for TekTips, so I'm keeping my crosses fingered.)

 

[SkipVought]

The working solution has no 'weakness' as the OP indicated.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}