Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop Down Boxes (2002 SP3)

    My spread sheet has a drop down box which displays the available selections from a list of company abbreviations.

    Would it be possible to display the company abbreviations and the list of full company names as well in the drop down list in a similar way as it can be done in Access?

    Example attached

    Regards

    Graeme

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Drop Down Boxes (2002 SP3)

    Hi Graeme,

    I managed to find this link...I have not tested it, but give the code a try...
    2 Column Combo
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Boxes (2002 SP3)

    Cheers Rudi

    At the risk of sounding ungrateful, this solution requires a combo box to be placed on the spreadsheet. What I'm looking for is the exact same solution except I need to use a drop down box from a spreadsheet cell rather than a combo box as I need to select from a list in every cell in a column.

    Regards

    Graeme

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Drop Down Boxes (2002 SP3)

    Here is a sample file if you need a working example...
    Attached Files Attached Files
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Drop Down Boxes (2002 SP3)

    Hmmm...AFAIK the validation cannot accept two columns. You can concatenate the two into a single and refer to that in your combo, but the resulting entry from the combo selection shows both values...

    I will have to search if there is a tweek for the validation option... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Drop Down Boxes (2002 SP3)

    This is a desprate solution, but have a look at the example...
    Attached Files Attached Files
    Regards,
    Rudi

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drop Down Boxes (2002 SP3)

    As Rudi has said, the only way you can do that with Data Validation dropdowns is to concatenate the data somewhere and use that as the list source. You could then use code to alter the entry as it is made if you require.
    Alternatively, you could use a forms combobox and move it to the relevant cell in code, but that could get a little ugly.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Boxes (2002 SP3)

    Rudi

    I'm not that desperate! Only kidding! Thanks for your hard work. The spread sheet forms part of a report so I can't change the look of it but I could do as you suggest and hide the extra columns. It is also used by a number of different people to enter data so it needs to be quite robust.

    I'll try your example and post back.

    Regards

    Graeme

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Boxes (2002 SP3)

    Hi Graeme

    A response from the worlds worst Excel user!!!

    I take a lot of advice from the people on the contextures webaite, it seems to me that this does what you are after

    http://www.contextures.com/xlDataVal02.html

    But then again, I probably am wrong (as usual <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drop Down Boxes (2002 SP3)

    Steve,
    Dependent lists are a different issue. Graham essentially wants a list showing a code and a description (like a 2 column combobox) , not one list dependent on the selection in another.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Boxes (2002 SP3)

    Thanks Rory

    I thought I may be off track

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Drop Down Boxes (2002 SP3)

    It's a good tip and a very useful site to be aware of though! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Boxes (2002 SP3)

    Steve

    Donít put yourself down! You probably know a lot more about Excel that I do. I do however, require a solution similar to as Rory describes.

    I can list the Abbreviations and the Company Names, concatenate them in a third column and display the selected concatenated result in a Validation List drop down box. Then I can Left String the result to display the original abbreviated Company Name in the adjacent cell.

    What I then need to be able to do is change the result of the drop down selection to match the abbreviated Company Name in the adjacent cell immediately after making a selection.

    If that doesnít make sense thereís an example attached. If that doesnít make sense then Iíll switch back to plan A.

    Regards

    Graeme
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down Boxes (2002 SP3)

    I'm not sure I understand your question, but does the following code in the worksheet module do what you want?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("A13:A18"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("A13:A18"), Target)
    If Len(oCell) > 3 Then
    oCell = Left(oCell, 1)
    End If
    Next oCell
    End If
    End Sub

  15. #15
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop Down Boxes (2002 SP3)

    Hans

    Thanks for your input. Thatís not quite what Iím after. My spread sheet has a column in which a Data Validation List drops down to display a choice of 7 different companies. The list is on a separate tab. The drop down displays an abbreviation for each company and enters that abbreviation in the cell after selection. An abbreviation is used to save space as the spread sheet is printed onto A4.

    What I would like to be able do is exactly the same thing except that when the drop down list is clicked and displays the available options, the full company name is displayed along with the abbreviation but only the abbreviation is entered into the cell after selection.

    Iíve done this in Access using a two column combo as Rory mentioned. I can display the company name and the abbreviation in a Data Validation List in Excel but I donít know how to do this and leave only the abbreviation in the cell after selection.

    Regards

    Graeme

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •