validation is not stopping invalid entries - creating a table with a dymanic data validation drop downs based on another drop down value

[bristolmark]

Hello really smart Excel guru's,

Excuse me as I'm new...

I have a table I am creating for a user to enter values as a record to generate a unique ID. However, the range of values able to be entered in each part of the table needs to change based on what type of record it is.

I have a worksheet with the record type schema as a list, other worksheets with various lists for the drop downs. The schema sheet dictates what list name is used for a particular field (via data validation list) in the table.

The schema name (record type) chosen in column 1 dictates the cell data validation. I've done this using an indirect vlookup function as list data validation (though more on that later, as I need a table with variable list data validation as well as variable length type validations for certain fields...)

It sort of works for the list data validation. I get different drop down list in a cell depending on the value selected in the drop down in column 1.

HOWEVER, if I enter a value maunally, instead of selecting from the drop down, it allows me to enter a value that is not on the list, and doen't produce a warning.

e.g. the the user data entry part of the table looks like this:
<table x:str="" style="border-collapse: collapse; width: 973px; height: 238px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 137pt;" width="183"> <col style="width: 58pt;" width="77"> <col style="width: 113pt;" span="2" width="150"> <col style="width: 96pt;" width="128"> <col style="width: 80pt;" width="107"> <col style="width: 77pt;" width="102"> <col style="width: 74pt;" width="99"> <col style="width: 48pt;" width="64"> <col style="width: 59pt;" width="78"> <col style="width: 51pt;" width="68"> <col style="width: 56pt;" width="74"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl43" style="height: 13.5pt; width: 137pt;" height="18" width="183">Promo Schema</td> <td class="xl40" style="border-left: medium none; width: 113pt;" width="150">Title</td> <td class="xl40" style="border-left: medium none; width: 96pt;" width="128">Classification</td> <td class="xl40" style="border-left: medium none; width: 80pt;" width="107">Promo Type</td> <td class="xl40" style="border-left: medium none; width: 77pt;" width="102">Market</td> <td class="xl41" style="border-left: medium none; width: 74pt;" width="99">Date (DDMM)</td> <td class="xl41" style="width: 48pt;" width="64">Duration</td> <td class="xl41" style="width: 59pt;" width="78">Version</td> <td class="xl41" style="width: 51pt;" width="68">Revision</td> <td class="xl42" style="width: 56pt;" width="74">Variable</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">CIty Homicide</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">PG style MA</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">CIty Homicide</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">PG style M</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">next</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">Getaway</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">PG style M</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">Getaway</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">PG style M</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">next</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">NRL Football</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">General</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">NRL Football</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">General</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">next</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">Premiership Football</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">General</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Promo</td> <td class="xl36" style="border-top: medium none;">Premiership Football</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">General</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">QLD</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0211</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl27" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Line Up</td> <td class="xl32" style="border-top: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0402</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl28" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt; border-top: medium none;" height="17">Line Up</td> <td class="xl32" style="border-top: medium none;">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">tonight</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">NSW</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">0402</td> <td class="xl26" style="border-left: medium none;" x:num="">20</td> <td class="xl27" style="border-left: medium none;">2</td> <td class="xl28" style="border-left: medium none;">
</td> <td class="xl33">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl35" style="height: 12.75pt;" height="17">Station ID (Sport)</td> <td class="xl36" style="border-top: medium none;">NRL Football</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">General</td> <td class="xl31">
</td> <td class="xl36" style="border-top: medium none;">VIC</td> <td class="xl30">
</td> <td class="xl26" x:num="">10</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl29">
</td> <td class="xl24">NQA</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl35" style="height: 12.75pt;" height="17">Station ID (Sport)</td> <td class="xl36" style="border-top: medium none;">NRL Football</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">General</td> <td class="xl31">
</td> <td class="xl36" style="border-top: medium none;">VIC</td> <td class="xl30">
</td> <td class="xl26" x:num="">10</td> <td class="xl27" style="border-left: medium none;" x:num="">1</td> <td class="xl29">
</td> <td class="xl24">NQB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl35" style="height: 12.75pt;" height="17">Station ID (Programme)</td> <td class="xl36" style="border-top: medium none;">CSI</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">G style M</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl30">
</td> <td class="xl26" x:num="">30</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl24">NETWK</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl35" style="height: 12.75pt;" height="17">Station ID (Programme)</td> <td class="xl36" style="border-top: medium none;">CSI</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">G style M</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl30">
</td> <td class="xl26" x:num="">20</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl24">NETWK</td> </tr> </tbody></table>
Promo Schema, Title, Classification, Promo Type and Market column cells are all drop down lists. however, depending on the value selected in Promo Schema, the lists in the other columns change.

For instance, depending on the Promo Schema value chosen (which is a standard data validation drop down list), the drop down list created in the title column has different title names available.

The data validation formula (which is a list type data validation) for columns Title, Classification, Promo Type and Market is:

INDIRECT(VLOOKUP(C$46, Schema_Lookup, MATCH($A47, Schema_Name, 0), FALSE))

;where it looks at the table column heading and retuns the list name for that cell from the relevant Promo Schema.

My main problem is that data can be entered into these drop down cells directly that doesn't match the list values, which breaks my ID lookup formulas.

Can anyone help, either by telling me why the data validation isn't doing it's job property. Or by suggesting an alternative method (which works properly)?

I'm open to using VBA but I have no skills in creating code from scratch. only copy/paste!

Thanks guru's!

Mark.

P.S. I'm more than happy to send anyone the excel file I am building as I appreciate it may be hard to see from the above description what I mean.

 

[bristolmark]

Anyone? I 'm sure there is a better way to do this, as my way seems to be giving me a bug....

 

[albasheer]

Does choosing the option STOP under Data Validation/Error Alert/Style do the job? This will limit data entry in the cell to selection from the menu. Otherwise I am not understanding the question.

 

[bristolmark]

Hi,

Yes those options are/were checked. It is not stopping invalid entries.

If I set the data validation list manually (to the list name) it works correctlly. i.e. invalid entries are met with an error message..

If I call the data validation list name from my vlookup formula (based on the Promo Schema chosen), it doesn't stop invlid entries.

The drop down list is correct in both cases. It's just the data validation that is failing.

Thanks.

 

[bristolmark]

I should add that my solution so far only allows me to dynamiclly change the data validation lists (in columns Title, Classification, Promo Type and Market). (with the above bug issue)

I also want to dynamically set the data validation for the other user entry columns (Date, Duration, Version, Revision and Variable). For those columns, the data validation aren't lists but are (variable) length condition validations.

I do not know how to do this bit yet...