Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    [attachment=85250:sample_data.xls]In my Excel 2003 workbook I have several worksheets that are named for each vendor. In the worksheets there are county codes. On my first worksheet I would like to create a drop down that shows me each vendor that has a certain county code. Then when I click on that vendor name I would like Excel to navigate to the worksheet for that vendor.

    I am attaching a sample worksheet that I hope will help explain what I would like excel to do.

    Thanks in advance for any assistance you can provide
    MOstate
    Attached Files Attached Files

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='MOSTATE' post='790760' date='26-Aug-2009 08:20'][attachment=85250:sample_data.xls][attachment=85250:sample_data.xls]In my Excel 2003 workbook I have several worksheets that are named for each vendor. In the worksheets there are county codes. On my first worksheet I would like to create a drop down that shows me each vendor that has a certain county code. Then when I click on that vendor name I would like Excel to navigate to the worksheet for that vendor.

    I am attaching a sample worksheet that I hope will help explain what I would like excel to do.

    Thanks in advance for any assistance you can provide
    MOstate[/quote]
    Hi
    I think you need to take another look at the design of your spreadsheet. Why are you planning on a separate worksheet for each vendor?
    I'd create a single Vendor Database sheet where all Vendor information is kept. That way extracting the info will be MUCH easier.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Each worksheet will have additional product information each individual vendor can provide. So a worksheet is needed for each vendor. Thanks for the advice though.

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='MOSTATE' post='790793' date='26-Aug-2009 09:37']Each worksheet will have additional product information each individual vendor can provide. So a worksheet is needed for each vendor. Thanks for the advice though.[/quote]
    So a List of Products per Vendor on each sheet?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The way your workbook is set up makes it very difficult. Perhaps you should consider using Microsoft Access instead of Excel for this.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    I am actually looking at this for a coworker and I suggested they use Access because the results could be achieved much easier in that application but they are insisting it be done in Excel. I'm sure they want it done in Excel because that's the product they know how to use. Is it possible in Excel?

    Thanks!!

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='MOSTATE' post='790798' date='26-Aug-2009 09:53']I am actually looking at this for a coworker and I suggested they use Access because the results could be achieved much easier in that application but they are insisting it be done in Excel. I'm sure they want it done in Excel because that's the product they know how to use. Is it possible in Excel?

    Thanks!![/quote]
    Yes it is, but just like data must be structured properly in Access the design of the spreadsheet in Excel is also critical.
    I'll try and post an example for you this morning.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I've attached a sample for you - it was created in 2007 but the principles are the same as 2003.
    You might also find this web page useful.
    If you do a search for dependent drop-down lists it should give some ideas.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    In your sample I don't see Excel navigating to Vendor 1 worksheet when Vendor 1 is chosen from the drop down. Can it do that?


    Thanks for the reply!!

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='MOSTATE' post='790805' date='26-Aug-2009 10:53']In your sample I don't see Excel navigating to Vendor 1 worksheet when Vendor 1 is chosen from the drop down. Can it do that?


    Thanks for the reply!![/quote]
    Oops forgot
    Yeah I'd use the hyperlink function to create a hyperlink on the fly see Post 728569
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    I can use the hyperlink function but I'm not sure how I can get it to hyperlink to a worksheet based on the name chosen from a drop down. I guess that's my main question, how do I get Excel to hyperlink to a worksheet based on criteria chosen in a cell?
    Thanks for all your assistance, your links have been very helpful

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='MOSTATE' post='790821' date='26-Aug-2009 14:27']I can use the hyperlink function but I'm not sure how I can get it to hyperlink to a worksheet based on the name chosen from a drop down. I guess that's my main question, how do I get Excel to hyperlink to a worksheet based on criteria chosen in a cell?
    Thanks for all your assistance, your links have been very helpful[/quote]

    The attached sample operates on code and does not require hyperlinks. It does however assume a heading row in each of the vendor sheets, and will present the lists in the same order as the Vendor sheet placement. It also requires that the Counties sheet be the first sheet.
    Attached Files Attached Files
    Regards
    Don

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='MOSTATE' post='790821' date='26-Aug-2009 14:27']I can use the hyperlink function but I'm not sure how I can get it to hyperlink to a worksheet based on the name chosen from a drop down. I guess that's my main question, how do I get Excel to hyperlink to a worksheet based on criteria chosen in a cell?
    Thanks for all your assistance, your links have been very helpful[/quote]

    Oops! My last post would not handle faulty County codes in the Vendor sheets. The attached workbook corrects this shortcoming.
    Attached Files Attached Files
    Regards
    Don

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='MOSTATE' post='790821' date='26-Aug-2009 12:27']I can use the hyperlink function but I'm not sure how I can get it to hyperlink to a worksheet based on the name chosen from a drop down. I guess that's my main question, how do I get Excel to hyperlink to a worksheet based on criteria chosen in a cell?
    Thanks for all your assistance, your links have been very helpful[/quote]

    Here is a sample using the HYPERLINK function.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks so much for both replies they both work great. I do have one more problem with the spreadsheet. Based on the county code that is entered in the first column the user wants the dropdown to populate with only those vendor names that service that county. Keep in mind that the county codes are not in the same cell address in any of the worksheets.

    Thanks again for the assistance you have already provided, you all are awesome!!

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
  •