Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an excel work book with three work sheets as bellow,

enter image description here

above sheet contains all the sales details (i.e. Colombo(city or region)) for each month and the names of each sales persons, there is another sheet like this as collections in the same format to record collected cash amounts

enter image description here

in the above sheet the column header is a drop down list which user can select either sales or collection.

What I need to happen is when user selects sales or collection from the drop down appropriate data should be displayed against each sales person's name, year and month should be able to change too. Amal, Kamal, Nuwan, etc. are the names of each sales persons.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

To get the total line from a name:

=VLOOKUP(NAME, A:N, 14, FALSE)

should do it, where NAME is the name to look up, A:N is the lookup table.

share|improve this answer
    
what does '14' means in this function? –  Harsha Apr 15 '12 at 7:06
    
14 is the distance between column A (the key column) and column N (the value column). –  Christian Mann Apr 15 '12 at 19:50
add comment

Sounds like a job for a pivot table. If you haven't used them before, pivot tables are the backbone of analytics where you can through data viewpoints around all of your data points. They allow you to take a flatfile sheet of data and manipulate it across multiple axes. Here's some links on the topic.

Learn to use pibot tables

Youtube tutorial

For your needs, you can create multiple worksheets with pivot tables for each salesperson, or each period of time, or sales region, or any other KPI or datapoint. When you update the source data, the pivot tables can be refreshed and keep in sync without any extra handling or manipulation from you.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.