Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
56 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 5
  • +1
16 Comments
 
LVL 24

Expert Comment

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

Accepted Solution

by:
Rob Henson earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 33

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
 
LVL 33

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 1000 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 33

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 33

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

636 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