?
Solved

Complex Excel (Somewhat)

Posted on 2014-04-17
3
Medium Priority
?
314 Views
Last Modified: 2014-04-28
I recently created an excel spreadsheet with a list of vendors and payments I have made.
What I am looking for do is bring create a drop down list and it will bring all those vendors into one area and give me a total cost.  

For Instance

          A             B          C             D             E                  F        
1  Location     Type    Month      Year       Amount       Vendor
2  New York    Data         11      2013         100.00     Time Warner
3 Ohio             Voice       11      2013           55.00       Verizon
4 Oregon         Data        12       2013           65.00      AT&T
5  New York     Data          1       2014         101.50     Time Warner
6 New York      Voice        1       2014            55.00      Verizon  


So If I want to create a drop down that if I wanted to select New York it will bring the entire row (1 and 5) and add the amount together and filter them.  Same with Month or Year, I want it so it adds it up totally.  Also same with Vendor, if I choose a vendor I want  it all to show up and break it down.  I know I can use the filter option but I am looking out of that so an drop down can happen and also it be nice to have a pie chart added as well would be a plus.
0
Comment
Question by:WooYing
[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
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 1002 total points
ID: 40010120
select the whole table including headings and 'insert' a pivot table. you will want to add location,year, month to rows and amount to the summarize data section. make sure its sum rather than count by right clicking and choosing field settings. you can add grand totals under the pivot table options. if you want to separate types put it as a page filter or column headings.
reg
0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 1002 total points
ID: 40010171
select the whole table including headings and 'insert' a pivot table. you will want to add location,year, month to rows and amount to the summarize data section. make sure its sum rather than count by right clicking and choosing field settings. you can add grand totals under the pivot table options. if you want to separate types put it as a page filter or column headings.
double clicking a total will show the rows involved, if you want a drop down filter you can either use page  filter or  put the name in as a column. basically just try all the option and layouts and I think you will fnd sonething that works
0
 
LVL 10

Assisted Solution

by:Bruce Denney
Bruce Denney earned 498 total points
ID: 40014924
Pivot Table as already will do the job, a pivot chart would also give you the pie or whatever but you may want to think about adding a column for year-month eg in G1 do =D1&text(C1,"-00") this will give to a year-month for each set, you could then chart things by year month and see a historical trend across years rather than looking at individual years in isolation.  Why force the leading zeros on months, why do years first? because then the year month when sorted alphabetically will also be sorted chronologically...
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

719 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