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.

2 Answers 2


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.

  • what does '14' means in this function?
    – Harsha
    Apr 15, 2012 at 7:06
  • 1
    14 is the distance between column A (the key column) and column N (the value column). Apr 15, 2012 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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