Lookup based on currently selected cell

[macummi]

Ok, unusual requirement this.

I have an excel spreadsheet which has a column of data. The data is
short description of an activity. ie:
Task 1
Task 2
Task 3
Task 4 etc

What I would like to do is have a cell elsewhere on the worksheet whic
then gives you a more detailed description of the activity.
So, if the task list is A1:A4 as above, I would like B1 to give
longer description of the activity, when one of the cells in the lis
is selected.

i.e. I want a single long description cell which changes its content
based upon which cell in the short description list is seleted (eithe
by mouse or keyboard)
I will have a reference table of long vs short references on
different worksheet.

Is this possible?

Best Regards,
Mar

 

[Gord Dibben]

macummi

You would probably need to use a Selection_Change Event to do this as you
describe. i.e. when you select a different cell.

Since you already have the short and long lists on another sheet, you could
use VLOOKUP and Data Validation to achieve your results.

An example only........

In Sheet2 column A enter the shorts(A1:A20)

In Sheet2 column B enter the longs(B1:B20)

In Sheet1 B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$20,2,false).

In Sheet1 A1 have a Data Validation drop-down box with a list generated from
Sheet2 Column A.

Note: to use a list from another worksheet you must name the list range.

In this example, the named range would be the shorts from Sheet2 Column A.

For more on Data Validation see Debra Dalgleish's site.....

http://www.contextures.on.ca/xlDataVal01.html

More notes: if you want to see more than one Short and Long in Sheet1 Column
A and B you can drag/copy the formula down Column B and use Data Validation
drop-downs in A1:A20. Same list in each. Select A1:A20 then
Data>Validation>Allow>List.

Gord Dibben XL2002