Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Drop down data (Office XP)

    Hello,
    I have a workbook with several sheets. One the first sheet I want one of the cells as a drop down list. The list data needs to come from sheet 2, columns a-c. How do I do this?

    Thanks,
    Mark

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down data (Office XP)

    You are only able to use a drop down list from data located on the same sheet.
    One way you can work around your problem is to reference the cells on Sheet2 somewhere on Sheet1.
    For example, in col IT of Sheet1 you can type in "=Sheet2!a1" (no quotations) and copy and paste as far as required. You then add your validation referencing IT1 to your last cell in Sheet1.
    Hope that is of some help.
    Stats

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down data (Office XP)

    Thanks,
    Another question is: After I do the drop down data, how do I copy some of the data to another cell on the same sheet? I want to pick a manufacturer from a list. After picking the Manufac. name, I want columns 2 and 3 to get copied to another cell on the same sheet.

    Thanks,
    Mark

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down data (Office XP)

    I'm not quite sure what you mean, do you want to pick a manufacturer from the drop down list and then have the corresponding info from Sheet2 show up on Sheet1.
    For example, let's say Sheet2 has Manufac in A, Address in B and City in C. Do you want to choose the manufac from the drop down list and then have the Address and City appear automatically on Sheet1?

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down data (Office XP)

    Sorry....
    I want to do as you have stated but assuming that I have all of the data on the same sheet now. I will pick the manufacturer and I want the data from columns 2 and 3 of the dropdown box to go to 2 other cells on the same sheet. I know how to do this in VBA for access. Is it the same sort of thing?

    Thanks,
    Mark

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down data (Office XP)

    It sounds like a VLOOKUP table on Sheet2 could meet your Address and City requirements. There is a fairly reasonable explanation of these in Excel Help.
    You may find that setting the "range lookup" to FALSE can meet your needs.

    HTH
    Gre

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Drop down data (Office XP)

    You should be able to do it with INDEX since the dropdown can give the the row number. (unless you are speaking of data validation list whihc would require a VLOOKUP)

    Also the lookup list does NOT have to be on the same page. Just reference the other sheet. If you want to do it with data validation, a named range gets around the same sheet requirement.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Drop down data (Office XP)

    Hi Mark
    Is possible to to do list of another sheet, this can be done, if your you define names of ranges and then in your list of validation, make mention to the name of the range

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Drop down data (Office XP)

    You can use data from other sheets as your dropdown data source.
    Just assign a range name to the data source and then use this name in your dropdown data validation.
    Click in the 'Source' box and press [F3] to list existing names assigned, point ad click the relevant name.

    zeddy

Posting Permissions

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