data validation list on changing pivot table

[dusoo]

Hi guys,

how can I create a data validation list from pivot table which data are changing?

1) first problem I have with Pivot table displaying "(blank)" row
2) second problem I have that the data val. list is displaying all blank rows from the range I specified and also the not wanted "(blank)" value

See attached xls.
excel_DataValidationList_PivotTable.xlsx

Thanks for any tips.

[FDibbins]

for 1, click the dropdown and uncheck BLANKS
for 2, chgange your DV formula to this...
=INDIRECT("$D$4:D"&COUNTA(D4:D157)+3)

[dusoo]

for 1, click the dropdown and uncheck BLANKS

Is there a way to do this without touching the pivot filter? When you uncheck the blanks, your pivot will not update with the new records, you need the select all, and uncheck it again... Maybe skip that line in DV formula? IS there a way?

Thanks

[FDibbins]

no, if you add new data, you need to refresh your PT. having blanks unchecked does not affect this

[:) Sixthsense :)]

Is there a way to do this without touching the pivot filter?

Yes it is possible

Place the cursor in D5 Cell and in Data Click A-Z sorting button, once you click the the blank will always stay at the bottom eventhogh any increase of decrease in data size. So refreshing the pivot will always keep the blank value in bottom only.

So like FDibbins suggestion use the blow formula in H3 cell validation List

Please Login or Register  to view this content.

Now it will always pick the right values....

[dusoo]

Thanks for that