Variable Data Validation from Pivot Table

[tricone]

Hi I am having a problem trying to make a List Data validation for a cell based on a pivot table.
I want to be able to input 'A' and 'red' and populate a cell with a data validation list with the choices "china' and 'uk'

any ideas?

Help is much appreciated
<table style="width:auto;"><tr><td><a href="https://picasaweb.google.com/lh/photo/jEcOmx8k71I0KKO50BQflQ?feat=embedwebsite"><img src="https://lh6.googleusercontent.com/-vVYrnImYDjM/ThXnbNmYJ0I/AAAAAAAAAfc/9SOlK_-hK2U/s800/pic.jpg" height="190" width="220" /></a></td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From <a href="https://picasaweb.google.com/108267593406594958298/Jul72011?authuser=0&feat=embedwebsite">Jul 7, 2011</a></td></tr></table>

 

[tricone]

any ideas?

 

[Jared_8878]

here is a solution, though it gets pretty hairy due to you having to do in formula range etc...

so if you have your pivot in E:G

so for instance "a" shows up in E6, blue red etc are in F:F and china usa show up in G:G


In I1 put a and in J1 put red
in n1 put in:

=IF(ROW()<=((MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1,7)&":"&ADDRESS(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1+30,7))),(INDIRECT(ADDRESS(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1,7)&":"&ADDRESS(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1+30,7)))),0))-(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1)+1),INDIRECT(ADDRESS(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$24,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$24,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$24,0)-1+ROW()-1,7)),"")

confirmed with ctrl+shift+enter
then copy down for a dozen or so cells or however many you think it might return at its maximum results.

 

[Jared_8878]

simplified it a bit.

=IF(ROW()<=MATCH(FALSE,ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$25,0)+1,6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$25,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$25,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$25,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$25,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$25,0)+50,5))),0),6))),0),INDIRECT(ADDRESS(MATCH($J$1,INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$25,0),6)&":"&ADDRESS(MATCH($I$1,$E$1:$E$25,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$25,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$25,0)+50,5))),INDIRECT(ADDRESS(MATCH($I$1,$E$1:$E$25,0)+1,5)&":"&ADDRESS(MATCH($I$1,$E$1:$E$25,0)+50,5))),0)-1,6)),0)+MATCH($I$1,$E$1:$E$25,0)-1+ROW()-1,7)),"")

in my notes for I1 and J1, you can do drop downs by doing separate pivots on those values so they are just a column of uniques, and do a list validation based on those ranges.

 

[tricone]

Jared- thank you very much for taking the time to come up with this solution.
I am having a problem with both codes - when I enter 'B' instead of 'A' as the first input it breaks the code. are you also having this issue?

also, are you applying this to a regular table with blanks or an actual pivot table?

 

[Jared_8878]

I had just done it to apply to your screenshot of three columns.

If you indeed have text below b (in your screenshot it shows "Grand Total" I believe) then entering b should not break it.

There has to be text below b in that column else it will not be able to create that last range - though in your screenshot you have text there so that should be working. If you don't have text below b - to test it, then put any word in E20 and F20 and you will see it work.

 

[tricone]

that is what I was missing it works fine now. Is it possible to output a column like this,
'blue
green
red'
given B as an input then use a data validation to choose between those colors and a second data validation to choose between the one or two possible countries?

I was looking to make this an automatically populated data validation since I have thousands of options in the spreadsheet i'm working on, but only 2-3 given a certain input.

sorry I didnt realize this was what I wanted when I posted this thread, but youre a saint for your help

 

[tricone]

Actually, I found a way to avoid a double validation. Thanks so much Jared.

 

[tricone]

any idea why this isnt working?

=IF(ROW()<=MATCH(FALSE,ISBLANK(INDIRECT(ADDRESS(MATCH(diam,$A$2:$A$1000,0)+1,6)&":"&ADDRESS(MATCH(diam,$A$2:$A$1000,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH(diam,$A$2:$A$1000,0)+1,5)&":"&ADDRESS(MATCH(diam,$A$2:$A$1000,0)+50,5))),INDIRECT(ADDRESS(MATCH(diam,$A$2:$A$1000,0)+1,5)&":"&ADDRESS(MATCH(diam,$A$2:$A$1000,0)+50,5))),0),6))),0),INDIRECT(ADDRESS(MATCH(prodline,INDIRECT(ADDRESS(MATCH(diam,$A$2:$A$1000,0),6)&":"&ADDRESS(MATCH(diam,$A$2:$A$1000,0)+MATCH(FALSE,IF(ISBLANK(INDIRECT(ADDRESS(MATCH(diam,$A$2:$A$1000,0)+1,5)&":"&ADDRESS(MATCH(diam,$A$2:$A$1000,0)+50,5))),INDIRECT(ADDRESS(MATCH(diam,$A$2:$A$1000,0)+1,5)&":"&ADDRESS(MATCH(diam,$A$2:$A$1000,0)+50,5))),0)-1,6)),0)+MATCH(diam,$A$2:$A$1000,0)-1+ROW()-1,7)),"")

I defined cells 'diam' and 'prodline'. used ctrl shift enter. data is in A2:C452

 

[bigmyk2k]

Actually, I found a way to avoid a double validation. Thanks so much Jared.

I am really glad that Tricone was able to solve the double validation problem, but I am still searching for a solution to this problem. Does anyone know a way to do this? Thanks!