Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2015-02-03
6
Medium Priority
?
828 Views
Last Modified: 2015-02-03
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?
0
Comment
Question by:Conor_Newman
  • 4
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586292
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40586306
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40586311
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40586315
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
 
LVL 2

Author Comment

by:Conor_Newman
ID: 40586334
ok trying that now
0
 
LVL 2

Author Closing Comment

by:Conor_Newman
ID: 40586384
Thanks for the help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question