Solved

Complex Excel (Somewhat)

Posted on 2014-04-17
3
279 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
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 the word count, how to display it, and what it breaks down to in Microsoft Word.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

744 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

8 Experts available now in Live!

Get 1:1 Help Now