how do i display text from on cell when i select another

[ravhic]

what i'm trying to do is have a dropdown list linked with a selected column, when i select a item from the drop down list, it displays text from another specified cell, or for it to display an abbreviation of the text selected in the drop down list? any help would be most welcome

 

[Ganjin]

hmm, I don't know how to do it directly, but you can try this if you want:

setup the list and put a vlookup next to it

ex.
A1 apples
A2 bananas
A3 coconuts

B1 05
B2 10
B3 15

C1 drop down list
C2 =vlookup(C1,A:B,2,0)

 

[ravhic]

Thanks but no luck with that!

 

[Ganjin]

curious?
perhaps I misunderstood what you wanted, can you go into a bit more detail as to what you wanted to achieve?
or what went wrong with what I suggested?

 

[ravhic]

i have serveral worksheet in a wookbook on the frist page i have a drop down list in one of the columns. It contains the correct names of the locations (which end up being to long for the printout). I have the drop down list on another page with the abreviation to the location in the column next to it. What i want is for when the user selects the location from the list it will display the abreviation instead of the full location name. If you can help it would be appreciated

 

[energman58]

You need to use an event procedure on worksheet change with the target your cells with the long name in

You need to select the cell you changed (which is returned by Target) and then

ActiveCell.Formula = Application.WorksheetFunction.VLookup(ActiveCell.Value, Range("I2:J6"), 2)

Where Range("I2:J6") is replaced by the address of your table where you match the long name to the abbreviation - long name in the first column abbreviation in the second.

The only tricky thing is that if you want to refer to a worksheet cell or range in the function you need to do it as a Range as shown above. It will return an error if your long name is not in your list so data validation needs to be used to constrain what teh users put in.

 

[ravhic]

thanks you do what ?

 

[energman58]

Event procedures are explained in here:

http://www.cpearson.com/excel/Events.aspx

What you need is a worksheet change event -

Private Sub Worksheet_Change(ByVal Target As Range)
'Some stuff in here
    End Sub

Any user change to your worksheet triggers this procedure and returns a value called Target which is a range you need to test to make sure it refers to the cell(s) you are interested in like this

If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then
        ' Target is within the range A1:C10. 
        ' Carry out some action. 
    Else
        ' No cell of Target in in the range A1:C10. Get Out.
    Exit Sub

So you need to put code that looks like this in the VBA Sheet where you are inputting the data (open the VBA editor with Alt-F11 and select the sheet name from teh window on the left) and then copy and paste the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub ' More than one cell chnaged. Get Out. 
'This makes sure that only one cell is passed to the lookup - you could modify the code to send a message that tells the user to only change one value at a time now it just stops
 
        If Not Application.Intersect(Target, Me.Range("A1:C10")) Is Nothing Then
        ' Target is within the range A1:C10 - you need to adjust this range to match your area of interest.
            Application.EnableEvents = False 'To make sure you dont get caught in an endless loop
            Target = Application.WorksheetFunction.VLookup(Target.Value, Range("I2:J6"), 2) 'You need to change I2:J6 to match where your table is
            Application.EnableEvents = True 'Turns event procedures back on for the next change
        Else
        ' No cell of Target in in the range A1:C10. Get Out.
        Exit Sub
       End If
    End Sub

Modify the ranges in my code to match your worksheet before you save it or you may get some very odd results!

Make sure Macros are turned on - you will need to save it to a trusted location as a macro-enabled workbook to make it run if you have 2007 or above thanks to MicroSoft's stupid security paranoia! And the job is done!

 

[energman58]

Sorry - minor mistake in the code you need to modify the line

Target = Application.WorksheetFunction.VLookup(Target.Value, Range("I2:J6"), 2)

To read

Target = Application.WorksheetFunction.VLookup(Target.Value, Range("I2:J6"), 2, FALSE)

As otherwise it will find only an approximate match and if you are looking at words it will simply find the first word that starts with the same letter - so for example Ohio and Oslo would both get matched to whichever one is first in the list.

Sorry - wasnt thinking