Rank a column depending on date selected

Hi Guys -

So I'm nearly there, but there's one bit which is annoying me.

My original forumula which ranks a fixed colum is:

=RANK(K2, K$2:K$191)+COUNTIF(K$2:K2,K2)-1

Which works fine. WHat I need to do next is match the column to a date (selected_date).

So far I have this:

=RANK(INDEX($B2:$Z2,MATCH(selected_date,$B$1:$K$1,0)), K$2:K$191)+COUNTIF(K$2:K2,K2)-1

Obviously this doesn't work because of the bold bits. What I need to do is apply the first bit to the whole column, e.g. K$2:K$191 needs to be rows 2 - 191 of whichever column I've selected, and the same for the COUNTIF bit.

Aaaargh!

Can anyone help?

Thanks,
John

Sorry to be a pain - does this make sense?

I guess I need to reference the whole row as well as a specific cell 0 and it's the row I can';t seem to figure out...

Would I need to post a sample spreadsheet?

Hi Sure (attached)

So this is my problem:

In the worksheet 'EU Funds', there are two columns of data in columns K and L for two different months.

In Column A of the same sheet, the data is ranked (currently based on column L, August data).

What I need to do, is use the selected date (in the Misc. Sheet, cell D19 - named selected_date_product) to rank a particular column.

If the selected_date_product is Aug-12, I need Column A to rank based on column L. If the selected date was July-12, rank based on column K.

So I guess the formula in column A:

=RANK(L2, L$2:L$191)+COUNTIF(L$2:L2,L2)-1

Rather than be fixed to column L, needs to change columns depending on the selected date.

When I can get the rank column to work, I can use VLOOKUP to pull the relevant entries based on the position in the Funds sheet.

Does this make sense!?

Thanks,
John

Is this okay? Apologies for the quality of the spreadsheet, I had to strip out lots of company-specific info...!

I think I've got it

If I put this in column A of the 'EU FuUnds' sheet and drag it down to the bottom cell, this seems to work:

=RANK(INDEX($B2:$Z2,MATCH(selected_date_product,$B$1:$Z$1,0)),INDEX($B$2:$Z$191,1,MATCH(selected_date_product,$B$1:$Z$1)):INDEX($B$2:$Z$191,190,MATCH(selected_date_product,$B$1:$Z$1)))+COUNTIF(INDEX($B$2:$Z$191,1,MATCH(selected_date_product,$B$1:$Z$1)):INDEX($B2:$Z2,MATCH(selected_date_product,$B$1:$Z$1,0)), INDEX($B2:$Z2,MATCH(selected_date_product,$B$1:$Z$1,0)))-1

Does this look right?