| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating drop down box with worksheet names
Is there a way to create a drop down box in a cell that contains the
worksheet names? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
Steve
If you want a macro see this message. http://snipurl.com/17t8d Gord Dibben MS Excel MVP On Thu, 18 Jan 2007 12:14:01 -0800, 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating drop down box with worksheet names
One more from Debra Dalgleish's site:
http://contextures.com/xlToolbar01.html Steve wrote: Is there a way to create a drop down box in a cell that contains the worksheet names? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| add data t drop down list use a different worksheet same workbook | Excel Worksheet Functions | |||
| Worksheet (tab names) in cells | Excel Discussion (Misc queries) | |||
| dynamic range name | Excel Discussion (Misc queries) | |||
| Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
| create a list of worksheet names (from a single folder, or open files) | Excel Discussion (Misc queries) | |||
Linear Mode
