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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Complex Excel (Somewhat)

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
WooYing
Asked:
WooYing
  • 2
3 Solutions
 
regmigrantCommented:
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
 
regmigrantCommented:
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
 
Bruce DenneySage 50 Consultant and IntegratorCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now