Solved

Complex Excel (Somewhat)

Posted on 2014-04-17
3
284 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 334 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 334 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 166 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now