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
373 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
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 …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now