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
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

740 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