Excel dynamic validation lists dependant on criteria given in another validation liust - please help

[knifey]

Hi,

Using a couple of different previous threads I have created dependant validation lists of unique values in Excel (e.g. choose a specific drug in column A then column B validation will show the different drug strengths for that drug only). The drug name and drug strength lists are in Excel tables so the validation lists can update dynamically by adding a new drug and strength to the bottom of the lists.
This works fine after I define the validation lists (e.g. data>validation>allow list (set formula)). But once I save the workbook, close and reopen the validation list shows only the first drug in the table. If I then go to data>validation>allow list>ok>ok (to redefine the validation formula) it works fine again. That is until I close and reopen the workbook.

I have the workbook containing my validation lists, tables, unique values formulas, etc. but am unsure how IU can post it on this forum?
Can anyone advise on where I may be going wrong with this?
Any help would be much appreciated.

Thanks,
K

 

[knifey]

Here is the link to my spreadsheet:

https://drive.google.com/file/d/0Bx5r9tIItS8VblRNY1pOdkh5LTA/edit?usp=sharing

Thanks,
K

 

[SkipVought]

hi,

Company security prevents downloads.

The drug name and drug strength [highlight]lists[/highlight] are in Excel tables so the validation lists can update [highlight]dynamically[/highlight] by adding a new drug and strength to the bottom of the lists.

What [highlight]LISTS[/highlight]? Relevant samples please.

What [highlight]DYNAMIC[/highlight] method are you using?

How are the relevant lists referenced in the DV--LIST?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

I'll try to be as thorough as possible.

1.) My list of drug names is setup in column R3:R254 of an Excel table in the worksheet named 'lists'.
2.) My list of drug strengths is setup in column S3:S254 of an Excel table in the worksheet named 'lists'.
3.) In column U3:U254 of an Excel table in the worksheet named 'lists' I have the formula:
{=INDEX(Molecule_Name,MATCH(0,IF(MAX(NOT(COUNTIF($U$2:U2,Molecule_Name))*(COUNTIF(Molecule_Name,">"&Molecule_Name)+1))=(COUNTIF(Molecule_Name,">"&Molecule_Name)+1),0,1),0))}
This is what is referenced in my first VL (drug name). The reason for this is to display only unique values.

These are both in the format:
col R col S col U
Asprin 50mg Asprin
Asprin 100mg Ibuprofen
Asprin 200mg #NA
Ibuprofen 100mg #NA
Ibuprofen 200mg #NA
etc.

4.) My first (drug name) VL has the formula:
=OFFSET(lists!$U$3,0,0,COUNT(IF(lists!$U$3:$U$254="","",1)),1)

5.) And my second (drug strength) VL has the formula:
=OFFSET(lists!$S$2,MATCH($B$2,lists!R3:R254,0),0,COUNTIF(lists!R3:R254,$B$2),1)

The problem is that it only works if I go to validation and click ok. Only then will it display the correct list. If I save it, close and reopen the VL will only display the first entry. Any ideas?

Thanks,
K

 

[knifey]

Hi Skip,

I forgot to say, the dynamic method I'm using is just Excel tables (i.e. a user adds to the bottom of the table j0 it's reflected in the VL straight away)

 

[SkipVought]

1) you need a UNIQUE list of drugs like
[pre]
DRUG
Asprin
Ibuprofen
[/pre]

2) you need a list to reference the drug with the strength, which you have
[pre]
DRG STR
Asprin 50mg
Asprin 100mg
Asprin 200mg
Ibuprofen 100mg
Ibuprofen 200mg
[/pre]

3) then using NAMED RANGES, based on the HEADING VALUES, the strength list based on the selected drug is
[tt]
=offset(str,match(SelectedDRUG,drg,0)-1,0,countif(drg,SelectedDRUG),1)
[/tt]
where SelectedDRUG is the NAMED RANGE of the cell containing the selected drug.

Don't know why you would use an IF statement in your DV???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

ahhh. I see what you're attempting now.

What does the range, Molecule_Name, refer to?

I avoid like a plague, generating a list via a formula that can return an error. I'd record a macro to generate a unique list, using MS Query, or Pivot Table of Advanced Filter, or COPY 'n' PASTE & Remove Duplicates. Many ways to skin the cat.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

That's what I was just thinking, this would be simple enough in VBA but has turned into a bit of a monster using formula (and I'm not too good at advanced formulas anyway).
When using userform comboboxes in VBA I use a looping .ADDITEM method (i.e. add unique items from a range). Do you knoow if there is a similar method for manipulating validation lists in VBA?

Thanks for your help,
K

 

[knifey]

Hi Skip,

I think you may have found the error. The range Molecule_Name refers to a named range that in my first attempt at this. I have then defined the Excel table on top of the named range.
I'll be able to test and confirm tomorrow.

That said I will still rewrite using VBA

Thanks,
K

 

[SkipVought]

You might do something like this:

If you are familiar with Worksheet Events, whenever you CHANGE a value in column R (the drug column), you 1) assign the same value to the next available cell in column U (your drug list for the first DV) and2) whenever you CHANGE a value in column U, you perform a Data > Data Tools > Remove Duplicates and then 3) SORT column U. Use your macro recorder the generate some of the code. Post back with your recorded code for help customizing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Or create a PivotTable of the Drug/Strength table (R:S).

Then whenever you CHANGE a value in column R (the drug column), you refersh the PT. Pretty simple!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]