Data validation lists from pivot table data

**KevinThomas** · 01-19-2010, 06:23 AM

Hi all,

I have created a drop down data validation list. The selections available are drawn from a pivot table.

The problem is have is that when the pivot table refreshes, it changes in length. Therefore my data validation list will either have loads of blank spaces at the bottom, or chop items off (as i have to select a specific cell area).

Is there a way of linking my data validation criteria, to the exact size of a pivot table and for that to increase/decrease as the pivot table refershes?

**DonkeyOte** · 01-19-2010, 06:28 AM

Consider defining the Validation range by means of a Dynamic Named Range - see link in sig. for more info.

If needed post a sample file.

**jomili** · 03-09-2010, 11:23 AM

I have a similar problem, but the posted solution doesn't seem to work for me. I am using a dynamic named ranged, and think I have the right formula for my data validation list, but I'm still gettting blanks. Here's my formula

=OFFSET(APR!$AA$5,0,0,MATCH("*",APR!$AA:$AA,-1),1)

and I've attached a sample document. I'd appreciate some help.

Thanks

**ChemistB** · 03-09-2010, 01:06 PM

You're including the four values in Col AA before AA5 in your Match. You need to subtract 4 like so

=OFFSET(APR!$AA$5,0,0,MATCH("*",APR!$AA:$AA,-1)-4,1)

Does that help?

**jomili** · 03-09-2010, 01:09 PM

A-Ha! All this time I was wondering why when I deleted the blanks from my data I still had blanks in the validation list. Thanks for setting me straight. Your formula worked wonderfully!

**royUK** · 03-09-2010, 03:30 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

**jomili** · 03-09-2010, 03:33 PM

Sorry Roy,

I was searching for an answer, rather wanting to post yet another repeated question, and came across this thread. Since this one was an identical problem, and had never been marked as solved, I figured it was still open, so I chimed in. Sorry to offend.