Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 1 down vote accepted

To get the total line from a name:


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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .