| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling Tab Name of a Different Tab Than What I'm Working In
Hi, right now I'm using the below formula to pull the tab name of the tab I'm
working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
Hi Jon
If a VBA solution is acceptable to you then this short piece of code will list all the sheet names for you, on whatever is the active sheet when you run the macro. Sub filltabNames() Dim ws As Worksheet, i As Long i = 1 For Each ws In ThisWorkbook.Worksheets Cells(1, i) = ws.Name i = i + 1 Next End Sub To Install Copy code above Alt+F11 to enter the VB Editor Alt+I+M to insert a new module Paste the code into the white pane that appears Alt+F11 to return to Excel To Use Alt+F8 Select the macro nameRun -- Regards Roger Govier "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon __________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
Do you know of any way to get it without VBA? I have a lot of users who will
get nervous about running a macro. Thanks again, Jon "Roger Govier" wrote: Hi Jon If a VBA solution is acceptable to you then this short piece of code will list all the sheet names for you, on whatever is the active sheet when you run the macro. Sub filltabNames() Dim ws As Worksheet, i As Long i = 1 For Each ws In ThisWorkbook.Worksheets Cells(1, i) = ws.Name i = i + 1 Next End Sub To Install Copy code above Alt+F11 to enter the VB Editor Alt+I+M to insert a new module Paste the code into the white pane that appears Alt+F11 to return to Excel To Use Alt+F8 Select the macro nameRun -- Regards Roger Govier "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon __________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
If you want a formula solution...
Create this defined formula Goto InsertNameDefine Name: SheetNames Refers to: =GET.WORKBOOK(1)&T(NOW()) OK Then, enter this formula in cell A1: =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)) Copy across as needed. -- Biff Microsoft Excel MVP "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
Hi Jon
As you are wanting to copy across Row 1 and not down column A, I think Biff meant his formula to say =INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),COLUMNS($A$1:A1)) -- Regards Roger Govier "T. Valko" wrote in message ... If you want a formula solution... Create this defined formula Goto InsertNameDefine Name: SheetNames Refers to: =GET.WORKBOOK(1)&T(NOW()) OK Then, enter this formula in cell A1: =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)) Copy across as needed. -- Biff Microsoft Excel MVP "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
I think Biff meant his formula to say
Yes. I had a bad day yesterday with my spelling/typing! Several typos in my replies. Thanks, Roger! -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Jon As you are wanting to copy across Row 1 and not down column A, I think Biff meant his formula to say =INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),COLUMNS($A$1:A1)) -- Regards Roger Govier "T. Valko" wrote in message ... If you want a formula solution... Create this defined formula Goto InsertNameDefine Name: SheetNames Refers to: =GET.WORKBOOK(1)&T(NOW()) OK Then, enter this formula in cell A1: =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)) Copy across as needed. -- Biff Microsoft Excel MVP "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
Thanks this works great! You're all making me look smart!
Jon "T. Valko" wrote: I think Biff meant his formula to say Yes. I had a bad day yesterday with my spelling/typing! Several typos in my replies. Thanks, Roger! -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Jon As you are wanting to copy across Row 1 and not down column A, I think Biff meant his formula to say =INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),COLUMNS($A$1:A1)) -- Regards Roger Govier "T. Valko" wrote in message ... If you want a formula solution... Create this defined formula Goto InsertNameDefine Name: SheetNames Refers to: =GET.WORKBOOK(1)&T(NOW()) OK Then, enter this formula in cell A1: =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)) Copy across as needed. -- Biff Microsoft Excel MVP "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pulling Tab Name of a Different Tab Than What I'm Working In
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Jon Ratzel" wrote in message ... Thanks this works great! You're all making me look smart! Jon "T. Valko" wrote: I think Biff meant his formula to say Yes. I had a bad day yesterday with my spelling/typing! Several typos in my replies. Thanks, Roger! -- Biff Microsoft Excel MVP "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Jon As you are wanting to copy across Row 1 and not down column A, I think Biff meant his formula to say =INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),COLUMNS($A$1:A1)) -- Regards Roger Govier "T. Valko" wrote in message ... If you want a formula solution... Create this defined formula Goto InsertNameDefine Name: SheetNames Refers to: =GET.WORKBOOK(1)&T(NOW()) OK Then, enter this formula in cell A1: =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)) Copy across as needed. -- Biff Microsoft Excel MVP "Jon Ratzel" wrote in message ... Hi, right now I'm using the below formula to pull the tab name of the tab I'm working in. I need to also pull in the subsequent tab names into each subsequent column. For instance cell A1 will have the current tab name but B1 would be one tab to the right and C1 would be 2 tabs to the right, etc. Can anyone help me? =RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$ A$1))-FIND("]",CELL("filename",$A$1))) As always, thanks! Jon __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate working days but change working week | Excel Discussion (Misc queries) | |||
| Pulling only first name | Excel Worksheet Functions | |||
| pulling out Numbers | Excel Discussion (Misc queries) | |||
| Pulling top 5 rank? | Excel Worksheet Functions | |||
| Pulling out every nth value | Excel Worksheet Functions | |||
![Jon Ratzel[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
