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
394 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel calculate based on 'x' in column 2 25
Copying from excel I am getting extra text 11 44
Problem to file 5 17
MIN per Month 8 24
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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

831 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