Creating tables based on looking down a column and back across a row

T

thecrow

Hi all, very much hope someone can help on this.

What I have is a list of clients in column A. Column B is a commission
rate. Columns C-N are Jan-Dec months.

Each client pays for a service at some point in the year - and the
value of that payment appears next to their name, under the column for
the appropriate month.

In another sheet, I'm creating a table, which has all payments for a
given month, which I want to generate based on whichever month I choose
via dropdown, and separate them by commission rate. Therefore, when I
choose "January" for example, I want it to populate two tables with all
the client names and the values they are paying - one table per
commission rate.

The problem I'm finding is the 'two stage' lookup - ie if I select Jan,
look up all the rows with a number under jan, and then give the names
in the far left on the same row.

Does that make sense?

I'll make a table below:


Client - - - Rate - - - Jan - - - Feb - - - Mar - - - Apr
A - - - - - - 0.01 - - - 100 - - - - - - - - - - - - - - - - - - -
B - - - - - - 0.01 - - - - - - - - - 500 - - - - - - - - - - - - -
C - - - - - - 0.01 - - - 200 - - - - - - - - - - - - - - - - - - -
D - - - - - - 0.02 - - - - - - - - - - - - - - - - - - - - - - 250
E - - - - - - 0.01 - - - - - - - - - - - - - - - 100 - - - - - - -
F - - - - - - 0.02 - - - 500 - - - - - - - - - - - - - - - - - - -

And then in my other table, when I select "Jan" for example I want it
to display:

(NB the headings will already be there)

Rate - 0.01
Client - - - Charge
A - - - - - - - 100
C - - - - - - - 200

Rate - 0.02
Client - - - Charge
F - - - - - - - 500

Hope someone can help on this! And I hope it makes sense! :s

Chris
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top