Highlighting cell / lookup

V

Vince

Hi all,

I want to modify a spreadsheet so that the user picks 1 variable from 4
different columns
and a cell that has the value equivalent to these 4 conditions is
automatically highlighted - all the data is within the same spreadsheet

For example, if you pick item A (variable 1), order 100 (variable 2
etc.)....then the value of this item is $100 (highlight this cell)

Thanks
 
M

Max

.. all the data is within the same spreadsheet

Data > Filter > Autofilter
seems the natural* fit here to achieve what you're after

*Also the easiest, as the autofilter dropdowns on the 4 key cols
will give your users the unique selections in the dropdowns

The ultimate filtered row(s) satisfying all 4 key col selections
will be the result line(s).
 
V

Vince

Max said:
Data > Filter > Autofilter
seems the natural* fit here to achieve what you're after

*Also the easiest, as the autofilter dropdowns on the 4 key cols
will give your users the unique selections in the dropdowns

The ultimate filtered row(s) satisfying all 4 key col selections
will be the result line(s).
Click to expand...

Sorry Max, that didn't work (was my first choice as well..) The data is
in 2 drop down lists -the combination of these 2 cells need to then
look up and return or highlight a value in a table. I have tried a IF
statement but I am not that advanced I'm afraid...

Thanks
 
M

Max

Vince said:
Sorry Max, that didn't work (was my first choice as well..) The data is
in 2 drop down lists -the combination of these 2 cells need to then
look up and return or highlight a value in a table. I have tried a IF
statement but I am not that advanced I'm afraid...
Click to expand...

Difficult to visualize what's happening there ..

Could you use either of the 2 free filehosts listed below to upload your
sample and
then post the link to it in response here (the link is generated when you
upload, just copy and paste it here)

http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click the "Browse" button, navigate to
folder > select the file > Open, then click the button centred in the page
below (labelled "Creer le lien Cjoint") and it'll generate the link. Then
copy & paste the generated link as part and parcel of your response here.

Kindly note that no attachments should be posted *directly* to the newsgroup
 
M

Max

Vince,

From your orig. post, if the value is to be returned from a certain col
range satisfying values for 4 variables (also defined/named ranges), then
something like this array-entered (press CTRL+SHIFT+ENTER) should work:

=index(ReturnCol,match(1,(Var1=val1)*(Var2=val2)*(Var3=val3)*(Var4=val4),0)

where:

ReturnCol = Named range for value to be returned
Var1 = Named range for variable 1 [Var2,3,4 similar]
val1 = The selected value for variable 1 [val2,3,4 similar]
- point to the DV cell for the variable

ReturnCol and Var1, Var2, Var3, Var4 should all be identically structured
and they cannot be entire col references [eg: A:A, X:X, etc]

The above construct works for unique matches.
It will return only the 1st match in ReturnCol which satisfies all 4
variables (if any).
 
M

Max

Biff said:
Here it is in English: ..
Click to expand...

Thanks for that, Biff. That should help the OP, if he is still interested in
pursuing the matter, that is. Unfortunately, I'm not able to access
google.com (or any links pointing to google.com) for the better part of a
normal day. cheers.
 
M

Max

Dang! Think poor "Tinyurl" has just been added to the access-restricted list
over here. Am going blinder by the day ..
 
M

Max

Biff said:
Here it is in English: ..
Click to expand...

Thanks for that, Biff. That should help the OP, if he is still interested in
pursuing the matter, that is. Unfortunately, I'm not able to access
google.com (or any links pointing to google.com) for the better part of a
normal day. cheers.
 
Top