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
48 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 31

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 31

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 31

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 31

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 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

16 Experts available now in Live!

Get 1:1 Help Now