Creating drop down box with worksheet names
Yes, that helps. Thanks.
"Ron Coderre" wrote:
Uh oh....typos!
Let's try that again:
The upside of a VBA solution is, of course, automation....which, in this
case, only translates into one less mouse click per sheet selection.
The downside, if it will only be used to drive the dropdown list, is that
every user will be prompted to allow macros to run (which can be a bit
unnerving in a small application).
So, that being said...
The below items are cell references in worksheets....not macros....with the
cell contents to the right of them.
eg H1: [MyAnalysis.xls]First!A1
.....means cell H1 contains the text "[MyAnalysis.xls]First!A1"
With
A workbook named MyAnalysis.xls
containing 4 sheets: Index, First, Second, Last
Then
On the Index sheet
G1: First Sheet
H1: [MyAnalysis.xls]First!A1
G2: Second Sheet
H2: [MyAnalysis.xls]Second!A1
G3: Last Sheet
H3: [MyAnalysis.xls]Last!A1
A1: (contains a data validation using G1:G3 values as the source)
B1: =HYPERLINK(VLOOKUP(A1,G1:H3,2,0), "Click HERE to go to that sheet")
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Steve" wrote:
Is the code listed below a macro? Not sure what G1: and H1: mean? A VBA
solution might be better. Thanks for trying ideas.
"Ron Coderre" wrote:
How about this.....
With
A workbook named MyAnalysis.xls
containing 4 sheets: Index, First, Second, Last
Then
On the Index sheet
G1: First Sheet
H1: [MyAnalysis.xls]First!A1
G2: Second Sheet
H1: [MyAnalysis.xls]Second!A1
G3: Last Sheet
H1: [MyAnalysis.xls]Last!A1
A1: (contains a data validation using G1:G3
B1: =HYPERLINK(VLOOKUP(A1,G1:H3,2,0), "Click HERE to go to that sheet")
Users would need to select a sheet
then click the link to go to that sheet.
Is that something you can work with
or do you need a VBA solution?
***********
Regards,
Ron
XL2002, WinXP
"Steve" wrote:
What I want is for all 10 worksheet names to be present when the drop down
box opens up. They can then scroll down and pick a worksheet and go to the
worksheet.
"Ron Coderre" wrote:
The short answer is: yes.....
But what you want to do with the names will determine which method would be
best for you.
Here's one way, using the CELL function:
This returns the sheet name for Sheet3:
=MID(CELL("filename",Sheet3!A1),FIND("]",CELL("filename",Sheet3!A1))+1,255)
Repeat for each sheet
One other quick note:
If you right-click on the sheet navigation arrows (just to the left of the
sheet tabs) you'll see the list of sheet names.
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Steve" wrote:
Is there a way to create a drop down box in a cell that contains the
worksheet names?
|