Pulling Tab Name of a Different Tab Than What I'm Working In

[Jon Ratzel]

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

 

[Roger Govier]

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 name>Run
--
Regards
Roger Govier

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

 

[Jon Ratzel]

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

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 name>Run
--
Regards
Roger Govier

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

 

[T. Valko]

If you want a formula solution...

Create this defined formula
Goto Insert>Name>Define
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.

 

[Roger Govier]

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

If you want a formula solution...

Create this defined formula
Goto Insert>Name>Define
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

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

 

[T. Valko]

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

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

If you want a formula solution...

Create this defined formula
Goto Insert>Name>Define
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

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

 

[Jon Ratzel]

Thanks this works great! You're all making me look smart!

Jon

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

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

If you want a formula solution...

Create this defined formula
Goto Insert>Name>Define
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


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

.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks this works great! You're all making me look smart!

Jon

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

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

If you want a formula solution...

Create this defined formula
Goto Insert>Name>Define
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


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

.