Excel Sheet Selector With No Macros

If you’re setting up a workbook for other people to use, they’ll appreciate it if you make it easy to move around in the file. You can create a table of contents on the first sheet, and that will get them off to a good start. But then what?

Once they’re on one of the other sheets, how can they get back to the table of contents, or go directly to a different sheet? We’ll add a drop down list of sheets, to make it easy to go to the one that you need.

Navigate With Shortcuts

To navigate through a large workbook, you can use keyboard or mouse shortcuts:

  • Ctrl+Page Up  to go to the next sheet
  • Ctrl+Page Down to go the previous sheet
  • Right-click on the sheet navigation arrows, to see a list of sheets

But how do you really find a sheet most of the time? I usually click the sheet navigation arrows, and look for the sheet name as the sheets fly past. That’s not too efficient!

The Finished Sheet Selector

To make it easy to go to a specific worksheet, we’ll build a drop down list that shows the sheet names. In the cell below that, a hyperlink that will take you to the selected sheet.

hyperlinknavigation05

Create a List of Sheets

The first step is to create a list of sheets that people need to go to. You probably don’t need to list all the sheets in the file – don’t include any Admin sheets where you keep the lookup lists, etc.

Tip: Since this list will be use for selecting a sheet, you could sort the sheet names alphabetically, to make them easier to find in a long list.

In my sample file, I listed four worksheets, and formatted the list as a named Excel table. The table is named tblSheets.

hyperlinknavigation03

Name the List

Next, I selected the list of sheet names (not the heading), and named the list – SheetList. I’ll use that name when creating the drop down list.

hyperlinknavigation02

Add a Drop Down List of Sheets

To let people choose the sheet they want to go to, you can use a data validation drop down list.

In this example, the drop down list will go in cell A1.

  • Select cell A1, and on the Excel Ribbon, click the Data tab
  • Click the top of the Data Validation command
  • On the Settings tab, from the Allow drop down, choose List
  • Click in the Source box, and on the keyboard, press F3, to open the Paste Name window.
  • Select the SheetList name, and click OK
  • Click OK to create the Data Validation list.

hyperlinknavigation08

Add a Hyperlink for Selected Sheet

Then, in cell A2, you’ll add a HYPERLINK formula to create a link to the selected sheet.

The first argument for the HYPERLINK function is the link location – the place that the link will take you

The second argument is the “Friendly Name” — the text that will show in the cell.

=HYPERLINK(“#'” & A1 & “‘!A1”,“Go to sheet”)

  • The pound sign (#) at the start of the address indicates that the location is within the current file.
  • This link will take you to cell A1 on the sheet that was selected from the drop down list.
  • The text in the link will say “Go to sheet”.

hyperlinknavigation07

Test the Link

To test the hyperlink, select a sheet name from the drop down list in cell A1. Then, click the link in cell A2, and you should go to the sheet whose name you selected.

Copy the Link

Once the drop down list and hyperlink are set up and working correctly, you can copy them to all the other sheets. Then, freeze the top 2 rows of each worksheet, so that the navigation cells are always visible.

Download the Sample File

To get the sample file, go to the Excel Sample files page on my website. In the Functions section, look for FN0044 – Hyperlink Formulas for Worksheet Navigation.

The sample file also has formulas to show the sheet name, and hyperlink formulas that will take you to the next or previous sheet.

hyperlinknavigation01

_______________

8 thoughts on “Excel Sheet Selector With No Macros”

  1. This tutorial is confusing at the most important part. Literally the one part that isn’t just “click here”, the part that shows how the hyperlink syntax should be written into a general form is unnecessarily convoluted. You shouldn’t have your A1 reference more than one thing, and it’d be helpful to actually write something about the function and how it works, not a few bullets about general info.

    1. This is the formula that goes into cell A2:
      =HYPERLINK(“#'” & A1 & “‘!A1″,”Go to sheet”)
      In that formula, the first A1 is a reference to the cell above the formula, where you select a sheet name
      The second A1 is part of the address where the hyperlink takes you — to cell A1 on the selected sheet.

      There are details on using the HYPERLINK function, and a video, on my main site:
      https://www.contextures.com/excelhyperlinkfunction.html#linksheet
      You can also download the sample file, to see how the hyperlinks work:
      https://www.contextures.com/excelfiles.html#FN0044

      1. Hi, I used the formula is works well. But how do i get it to automatically select the next empty cell in column A. I do not want it to keep selecting A1

  2. Hi,
    I have 100 sheets in my workbook. I want a drop-down list of sheet names, so that the respective user can select the required sheet and then update. It would be nice if the list is displayed just besides the first sheet name.
    Else, we can create the list on a separate sheet and call the individual name to display the sheet for the respective user to update.

  3. Or, you could just right-click the arrows next to the sheet tabs. This brings up a list of all sheets and allows you to go to any sheet, quickly.

  4. I’ve been using the formula =HYPERLINK(“#”&”‘”&A14&”‘”&”!$a$1″,”<— Go to this worksheet") for several months and it works great.

    My one frustration is I often work with two or more windows on the current file and the link would always take me to window1. My preference is to have summary information on the left monitor (window 1) and detailed information on the right (window 2).

    I finally solved this with the following macro:

    Sub BudgetWindow2()

    Dim shname As String
    shname = ActiveCell.Value
    Windows("Budget Import Tool 2022.xlsx:2").Activate
    If WorksheetExists(shname) Then
    Sheets(shname).Select
    Exit Sub
    End If

    Do Until WorksheetExists(shname)
    shname = InputBox("The cursor was not in a cell containing a sheet name. Enter sheet name")
    If Not WorksheetExists(shname) Then MsgBox shname & " doesn't exist!", vbExclamation
    Loop
    Sheets(shname).Select
    End Sub

    I'd love to know if there's a way to click on a hyperlink and then be prompted to indicate which window you would like it to open in.

  5. Hyperlinks and such are not particularly needed anymore and not very flexible for that matter.

    For a fair while now, if there are cell references in a cell and you double click the cell, Excel will select the cell references. That’s behavior subject to some differences based upon do they all exist on the same page as the cell you doubleclicked or not, but basically:

    1. If they are all on the same page as the cell you clicked, they all will be selected. One range I use is ” =$E$2:$J$42, $S$2:$W$42, $E$1, $Q$2:$Q$11 ” to select data entry cells on a page so I can press delete to clear them all and start again. I want to begin in E2, so I specify it first. Used to be you had to specify it last, which was a general Windows issue even manipulating files, but that’s fixed up.

    If you want to specify them in the order a person should use them, you can and if they press ENTER or TAB to move, keeping the highlighted selection highlighted, they will move in the order you list the cells/ranges.

    2. If different pages of the same spreadsheet are involved, and the first cell referenced is on a different page, the doubleclick will move to that page and the first cell listed. Handy for a Table of Contents, but not for marking and making use of a complicated range, like above. If a cell on the same page is listed FIRST, or multiple cells on the same page as the “clicking cell” are, it will go there (one cell) or mark the list of them (more than one cell listed on the same page). It’s that they are on that same page, not their order of listing. So two on the same page, one on a different one, and another on the same page will see the three on the clicking page selected.

    3. If the cell reference being acted upon is a different workbook, no problem. It will even open it for you. Password protection still operates, so presumably so do other, related, protections.

    But it’s as simple as listing the cells, or working out an order to list them if wanting to guide data entry.

    And an easy ad hoc (or planned) way to, say, connect two disparate places. Like you are looking mostly at D238:M431 but now and then need to go to A43 or R9936, and then back. Just quickly type “=A43” into a cell close to the main area and doubleclick it. You go to A43. Look at it, then type a quick “=D238” (perhaps) and doubleclick IT to return. Rinse and repeat.

    For a Table of Contents, use something like:

    =IF( TRUE, “Introduction”, Sheet2!C5 )

    which will SHOW “Introduction” in the cell but go to Sheet2, cell C5 when doubleclicked.

    Another handy thing about it is IN CONJUNCTION WITH the Hyperlink function for send and return. If you need send and return ability (and can plan for it, so not using the ad hoc method above), two Hyperlinks have a problem… send works nicely. But return reads the cell formula in the original cell and continues on back to where send put you. You can’t go back. Two of these though… Excel will tell you about the circularity, but the doubleclicking still works just fine. Try it with Hyperlink, or Hyperlink and one of these, and it will bust open. Even after removing the circularity, the Hyperlink will still not work. Just a dead lump.

    I don’t personally use it for much outside of marking complicated ranges for content deletion or other use. (Really handy when the places one must enter data are widely separated, by the way.) One Table of Contents I use isn’t in my control and Hyperlink is better for it anyway as it dynamically chooses a landing spot (which would ruin this technique as it would select things like the range COUNTA is used on for choosing that spot, not the spot itself). But I do use it ad hoc in a lot of circumstances.

    Complicated range creation is not hard, by the way. Either just type “=” and begin clicking, making sure you get a comma between each reference, or even use string-building techniques to let Excel do the heavy lifting.

    1. Thanks, Roy, and to use this technique, people will have to turn off the option to edit directly in cells
      To do that, at the top of Excel, click File, then click Options
      Next, at the left, click Advanced
      Then, in the first section, Editing Options, remove the check mark from “Allow editing directly in cells”

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.