reference Pivot Rows in Data Validation

Question

I have a table of data. I am summarizing this via a pivot table (e.g. 1000 rows of data only has 10 locations identified).

I would like to reference the 10 locations via data validation in another table.

for example, over time the data table may create an 11th location, the pivot table would pick that up and the drop down would be updated automatically with this new option.

I have tried 'indirect' but can't get the right reference and not sure I can get 'getpivotdata' to do what I need either.

Any help would be gratefully appreciated.

Regards,

Jim

Answer 1

Interestingly, I'm having some trouble refreshing a sample data pivot table... but that isn't your question :)

In the column of your pivot table row headers, if you have a totals row, use something like:

=MATCH("Grand Total",D:D,FALSE)

to get the last row with data. 

The top of your pivot always starts in the same place, so subtract the fixed number of rows until your data starts (and do this in an empty cell to make sure it matches the number of rows you expect)

=MATCH("Grand Total",D:D,FALSE)-4

Then make a named range that uses offset from that first cell, something like

=OFFSET(D4,0,0,G2,1)   where G2 is the formula above- you can just embed it all in the named range if you include the sheet reference, like

OFFSET(D3,0,0,MATCH("Grand Total",Sheet1!D:D,FALSE)-4,1)

This assumes that you don't have blank rows in your cell range, as they would also show up in your DV list

The info above may not have perfect syntax, but should get you pointed in the right direction. When I manually refreshed my sample pivot, the offset range expanded/diminished when the number of items changed

Answer 2

Hi JWaldronGDC,

Does Karetl's solution work for you?

Regards,

Fraser 

Yep, it worked a treat. The drop down now changes (grows and shrinks) based on the pivot table.

thanks Karetl!

Answer 3

Hi JWaldronGDC,

Does Karetl's solution work for you?

Regards,

Fraser

Answer 4

Thanks Karetl! I understand the logic you've applied and I'll try it in the morning!