Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Complex Excel (Somewhat)

Posted on 2014-04-17
3
Medium Priority
?
319 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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
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
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

885 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