Create A User Friendly Sheet Index For Worksheet Navigation

  • Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.

  • Re: Go To Sheet Selected From List In Workbook


    Hiya Def,


    Consider carefully the information in the following thread (and the threads mentioned within the thread).


    http://www.ozgrid.com/forum/showthread.php?t=22806


    Alternatively, if you are struggling with this...


    If youre sheet name is to be constructed from 2 cells in Column A and Column B (and assuming those cells are side by side (say A1 and A2), then you can use the hyperkink function in Column C, Row 1 as follows:


    =HYPERLINK("[book1]" & A1 & B1 & "!A1","Jump to Sheet named " & A1 & B1)


    So if your book name is "book1.xls" and A1 contains "tdm-" and B1 contains "216" and C1 contains the hyperlink function above, when when C1 is clicked on, it will jump to Cell A1 in Sheet tdm-216.


    HTH
    GEr

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Go To Sheet Selected From List In Workbook


    Alternatively, if you MUST have a macro to jump to the sheet of the two cells selected by the user and assuming these two cells are side by side, then put the following macro on a button...



    Not the best option though IMHO (You should use the List box solution I showed you above). Take care.


    HTH

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Go To Sheet Selected From List In Workbook


    Ger Plante, thanks for your input. I did go with the code and a button, which worked fab. It just seems redundant to have a list in the workbook and then have to go select from a duplicate in a list box / pop-up. Thx again.

  • Re: Go To Sheet Selected From List In Workbook


    Quote

    There will be over 100 sheets in the workbook

    That's 99 too many. Excel Best Practices


    BTW, if you right on the Sheet scroll arrows, you will a user friendly list of Worksheets to click on.

  • Re: Create A User Friendly Sheet Index For Worksheet Navigation


    Thx Dave, I certainly do follow your guidelines most of the time and recommend them to others also but I felt the business scenario dictated the multiple sheets (partially due to change impacts / resistance). However, I foolishly was not aware of that right-click on the scroll arrow functionality - that is a great tip! Many thx. Thread is solved.

  • Re: Create A User Friendly Sheet Index For Worksheet Navigation


    Quote


    if you right on the Sheet scroll arrows, you will a user friendly list of Worksheets to click on


    Didnt know that.... :confused:


    Nice tip.


    Hey Dave, is there a dialog number for that, so that it could be triggered with code -

    Code
    Application.Dialogs(???).Show


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!