Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

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

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
agwalsh
Asked:
agwalsh
  • 5
  • 5
  • 5
  • +1
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No attachment - and yes, it's possible.
0
 
Rob HensonIT & Database AssistantCommented:
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
 
J2FCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
agwalshAuthor Commented:
Ah yes the "I didn't put in the damn attachment" gremlin strikes. Attachment should be with this one :-)
dropdown-for-worksheets.xlsx
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
agwalshAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Cell A3 is a drop-down list.

Click inside A3 to see it.
0
 
Rob HensonIT & Database AssistantCommented:
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
 
agwalshAuthor Commented:
@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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
agwalshAuthor Commented:
@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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can attach it to a button.
0
 
Rob HensonIT & Database AssistantCommented:
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
 
agwalshAuthor Commented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now