Lookup based on conditional formatting?

**himey77** · 02-21-2011, 02:36 PM

Originally Posted by Greg M

Take a look at the attached workbook.

Column D contains the percentages which correspond to the options selected by the user.
Cell D4 contains the maximum value of these percentages.
Columns A and B are conditionally formatted so that the option and percentage cells corresponding to this maximum percentage are highlighted.

Pressing the F9 key recalculates the various random numbers on the worksheet, and the appropriate option and percentage cells are again highlighted

I hope I've understood your requirements and that this helps - please let me know how you get on.

Regards,

Greg M

Now I need C5 to be populated with the highlighted result from Column A.

**ConneXionLost** · 02-21-2011, 02:44 PM

In C5 use:

=INDEX($A$6:$A$38,MATCH(D4,$B$6:$B$38,0),1)

Cheers,

**himey77** · 02-21-2011, 04:24 PM

Originally Posted by ConneXionLost

In C5 use:

=INDEX($A$6:$A$38,MATCH(D4,$B$6:$B$38,0),1)

Cheers,

Thank you. That worked, but my issue has become more complicated.

I now need to keep the data in Column C, add new columns, and have columns A & B randomized/highlighted based on the data populated ("x") in the rightward most column.

Please see attached. In this example, C5, D5, and E5 would be populated based on the index formula and would hold their relevant options.

There may even be an easier way to accomplish this, I just want to avoid having the user clear out the X's in each subsequent column.

**ConneXionLost** · 02-21-2011, 06:26 PM

himey77,

Is this what you intend?

**himey77** · 02-22-2011, 11:30 AM

Originally Posted by ConneXionLost

himey77,

Is this what you intend?

Unfortunately, not. I need the "Chosen SPP" field to be locked in once data is added to the next column. For example, once the user has acheived a result in C5, that result will be locked into C5. The user will then move on to Column D, select their options and acheive a result in D5. That result will be lock into D5. The user will then move on to Column E... etc. etc.

**ConneXionLost** · 02-22-2011, 12:26 PM

From the first post:

Originally Posted by Greg M

...Pressing the F9 key recalculates the various random numbers on the worksheet, and the appropriate option and percentage cells are again highlighted...

The RAND() function will give you a new set of data every time the spreadsheet recalculates. As long as you're relying on the RAND() function as the source of your data, it will place the problem of "locking in" the selected values well beyond the capabilities of any LOOKUP formula or conditional format solution. Especially if you intend to do this with multiple examples.

I recommend you begin a new thread with the suggested title of: "VBA method of copying values from RAND() to a range based on values in another range".

Hopefully one of the VBA experts here can help you.

Good luck,

**himey77** · 02-22-2011, 01:47 PM

Originally Posted by ConneXionLost

From the first post:

I recommend you begin a new thread with the suggested title of: "VBA method of copying values from RAND() to a range based on values in another range".

Hopefully one of the VBA experts here can help you.

Good luck,

I will do just that. Thank you for your help!