Solved

Put a dropdown list that allows me to quickly go to another existing worksheet (and will autopopulate with new worksheets)

Posted on 2015-01-30
16
49 Views
Last Modified: 2016-02-11
hi Folks
wondering how to do this...and wondering if it's possible to do with formulas rather than code. I've attached a file with 3 worksheets - Larry, Curley and Mo. How can I set up a dropdown list on each sheet e.g. on Larry - that will show me Curley and Mo and that will bring me to that sheet if I click on it. And if I decided to add to my motley crew e.g. Laurel, Hardy etc it will automatically update.
Thanks as always :-)
0
Comment
Question by:agwalsh
  • 5
  • 5
  • 5
  • +1
16 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579326
No attachment - and yes, it's possible.
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 40579345
If you right click on the sheet navigation buttons at the bottom left  ( looks sort of like this "|< < > >|"), you will get a list of all sheets in the workbook. Click on which sheet you want.

I have experimented with the number of sheets it can handle and it seems it will go to 16 before having the last option of "More Sheets" clicking on which gives you the whole list in a scrollable window.

No need for formulas or VBA, just uses existing functionality.

Thanks
Rob H
0
 
LVL 1

Expert Comment

by:J2F
ID: 40581761
The number of sheet navigation buttons is limited by a function of; 1. the length of the worksheet name, 2. the width of the monitor and 3. the size of the horizontal scroll bar.

On a 26" wide monitor, with a very minimal scroll bar, and using default sheet names (Sheet1, Sheet2, ...), I was able to see 25 tabs.

Shorter names = more tabs.
Wider scroll bar = fewer tabs.

running Excel 2013 (64bit) under Win 8.1 Pro (64bit)

Jim
0
 

Author Comment

by:agwalsh
ID: 40585945
Ah yes the "I didn't put in the damn attachment" gremlin strikes. Attachment should be with this one :-)
dropdown-for-worksheets.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586003
Please find attached what I would do.

There is a new spreadsheet called "Sheets", in which you can add any new sheets.
dropdown-for-worksheets150203.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40586016
Why re-invent the wheel? The functionality is already built in with the Sheet navigation toolbar.

If you do really want the VBA route, Phillip your suggestion only links from one sheet to one other sheet. For example, sheet Curley only has link to Larry, what about link to Mo?

Thanks
Rob H
0
 

Author Comment

by:agwalsh
ID: 40586043
What I had in mind was a combo/dropdown list on each sheet that would quickly navigate to another sheet by picking it from the list and to have the data source be dynamic so that as other individual sheets would be added...they would populate the dropdown...thanks :-)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586044
Cell A3 is a drop-down list.

Click inside A3 to see it.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 40586077
Phillip, apologies I hadn't spotted that!

But still, why not use built in functionality?

Drop down list - you have to click on,
Sheet navigation - you have to click on.

With drop down you have to add the sheets to the list, the navigation tool gets them automatically. If you hide a sheet, it no longer shows in the list so someone can't select it by accident, you would have to remove it from the list because a hyperlink to a hidden sheet doesn't cause an error but it doesn't go to the sheet either.

Thanks,
Rob H
0
 

Author Comment

by:agwalsh
ID: 40588118
@Philip, I've had a look at this and I really like it so far...but I know that new worksheets will be added so I'm curious as to how I could autopopulate the list of sheets in the Sheets tab....
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40588121
You can use this code:

Sub PopulateSheets()
Dim introw As Integer
introw = 1
For Each sht In Sheets()
    Select Case sht.Name
    Case "Sheets"
    Case Else
        Sheets("Sheets").Cells(introw, 1) = sht.Name
        introw = introw + 1
    End Select
Next
End Sub

Open in new window

0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40588156
Agwalsh - have you looked at the built in functionality of the sheet navigation toolbar?

It does exactly what you want without extra cells, lists or sheets.

Thanks
Rob
0
 

Author Comment

by:agwalsh
ID: 40588306
@Philip Burton - do I attach this code to a button or where do I put it?  @Rob henson - will mention that about the sheet navigation tool bar...thanks :-)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40588440
You can attach it to a button.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40588525
In addition, fighting my corner as they say, the hyperlink option suggested by Phillip will always go to the specified cell.

The navigation toolbar goes to the sheet and leaves the cursor where it was when that sheet was last viewed.

Thanks
Rob H
0
 

Author Closing Comment

by:agwalsh
ID: 40602562
As it happens, we chose to go a different route - I suggested using pivot tables instead and that got around the dropdown issue :-) but I really liked Rob's point about the navigation tool...I had come across it before but forgotten...but will remember it now. Thanks as always :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now