Finding a cell reference in an array

Hi all,

I'm setting up an Excel (2000) sheet to record the results of a competition at my local photographic society. I'm trying to make it as simple as possible to fill out by the people using it, so have shaded the cells that require the user to put data in. I'll post a screenshot of the sheet, which is named "DPI", to make it easier:

(EDIT) screenshot removed - see attached file in post #3 below instead

I use one row for each member entering the competition. Each member can submit up to three photographs for judging. The names in B5:B30 are tied to the member number in A5:A30, and pulled in with VLOOKUP from a separate sheet containing all the club's members. The names in this example are, obviously, fakes. ;-)

The three blocks are to enter the image titles and associated scores. I have a range called "scores" which is defined as =DPI!$D$5:$D$30,DPI!$G$5:$G$30,DPI!$J$5:$J$30

The rank columns are simply calculated using the RANK function. E.g. the formula in E5 is =IF(ISBLANK(A5),"",RANK(D5,scores,0))

The cells at the top are calculated using the LARGE function upon the defined range "scores" (although they could equally use the SMALL function on the rank columsn instead).

The bit I'm stuck at is what I want it to say along the top. eg: "Leg 1 Winner: 30 is {insert image title} by {insert member name}"

Using the winner as an example, I want to take the winning score shown in D1, find it in the named array "scores", and return the image title to the left of it. Obviously I can't use VLOOKUP as the data is to the left of the lookup value as well as being in multiple columns. I wanted to use OFFSET to return the value of the column to the left, but to do that I need to pick up the reference of the cell containing "30" i.e. the winning score shown in cell D1.

How can I find this cell reference? Or is there a better way? I thought about the old MATCH/INDEX function, but INDEX doesn't seem to work very well with data in non-contiguous arrays as I'd have to specify which block to look in.

Once I can get the image title returned I hopefully sholdn't have any bother extending it to return the member name from a fixed column, but I can't figure out how to get the reference of the cells in the "scores" array that contain the winning scores shown in D1, D2 and D3.

Many many thanks in advance for any help! This has been driving me crazy for the past couple of days!

Post workbooks instead of pictures, when you can.

In F1, try this:

=INDEX($C$5:$C$30, MATCH(1, $E$5:$E$30, 0)) & " by " & INDEX($B$5:$B$30, MATCH(1, $E$5:$E$30, 0))

You've designed yourself into a corner here. Life is so simple if you create a standard database where each row represent ALL the information for a single entry.

This is how I would set up the same data for simplified evaluation.

I've added some data validation so if you select the same Member # more than 3 times, the row protests.

The totals in G and H will only appear in the first row for each member #. Not sure what you want to do with that data, but there it is.

There's a dynamic drop down in column A for selecting the Member #, as you add to the list on the Members sheet, that drop down expands itself.

There's no Autofilter active on this sheet. What are you referring to?

On sheet1 column A are your member numbers. As you add to that list, the drop down on sheet 2 will expand itself to include all your sheet1 entries.

The drop down is a Data > Data Validation > List.
The Source: is a named range called Members. I created the named range on sheet1. Look in Insert > Name > Define and click on Members to see the dynamic range formula being used.

We could just make a range with a regular range of cells, but then your drop down would have a bunch of blanks, so the dynamic formula figures out how many entries are in column A and expands to show just that many options.

I thought this would be more attractive, change the DPI formula in cell G2 to this, then copy it down:

=""""&UPPER(INDEX($C$7:$C$32, MATCH(ROW($A2), $F$7:$F$32, 0))) & """ by " & INDEX($B$7:$B$32, MATCH(ROW($A2), $F$7:$F$32, 0))

Leave it the way it is if you don't like this new appearance.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].