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
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