Excel Data Validation List drop down from Pivot table in Col A, conditional on value of Column C

Ok,

So in Wbk A, I have a worksheet(Sht1), it has on it a pivot table that feeds from a list in another workbook (wbk B).
The pivot table is in Column A, Column B has some vlookups to populate based on the value of Col A and Column C has  either no value or a 1 for each line.

On another worksheet(sh2) still in wbk A, I have a drop down list that currently is populated from the Pivot table on sht1, column A. I want this to be conditional, so the drop down will only show the values from Column A pivot table IF there is "1" in column C.

Does that make any sense to anyone?
LVL 2
Conor_NewmanAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Yes - I would.

You can create a PivotTable based on a PivotTable and other data, but it's unobvious.

Do not click inside the PivotTable. Instead, go to a different cell,
THEN go Insert - PivotTable,
THEN select the range.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It makes sense, but difficult to advise.

My first question is - do you actually need the VLookups in Column B? And where does column C calculate from?

Basically, I'm rounding if a second PivotTable can be made that can be used as your Data Validation List.
0
 
Conor_NewmanAuthor Commented:
Right, Column C is populated from a macro, on opening the workbook, it checks the user, then fetches an array listing all the Projects that user is allowed access to from a master list, it checks each of the items in the array off the list in the pivot table and places a 1 in Column C on Projects that the user is allowed access. So possibly, conditional drop isn't possible? I might be best just creating a second pivot table in say column D and feed the Drop downs from that?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Conor_NewmanAuthor Commented:
oh and yes, column B basically fills in the title of the projects listed in the pivot table, which is used as a reference table elsewhere in the workbook.
0
 
Conor_NewmanAuthor Commented:
ok trying that now
0
 
Conor_NewmanAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.