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

J

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
 
R

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

Jon Ratzel said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
J

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




Roger Govier said:
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

Jon Ratzel said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4825 (20100201) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...
 
T

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.
 
R

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

T. Valko said:
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


Jon Ratzel said:
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
Click to expand...



__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ 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

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


Roger Govier said:
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 said:
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


Jon Ratzel said:
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
Click to expand...



__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...
 
J

Jon Ratzel

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

Jon



T. Valko said:
I think Biff meant his formula to say
Click to expand...

Yes. I had a bad day yesterday with my spelling/typing! Several typos in my
replies.

Thanks, Roger!

--
Biff
Microsoft Excel MVP


Roger Govier said:
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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...


.
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Jon Ratzel said:
Thanks this works great! You're all making me look smart!

Jon



T. Valko said:
I think Biff meant his formula to say
Click to expand...

Yes. I had a bad day yesterday with my spelling/typing! Several typos in
my
replies.

Thanks, Roger!

--
Biff
Microsoft Excel MVP


Roger Govier said:
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
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tab Formula from diff sheets 1
Get tab name formula 3
Vlookup and return sheet name also 2
Find and return name of worksheet tab for referenced cell 2
How do I link 'tab names' to a cell in Excell spreadsheets 3
Fill cell with a worksheet name? 5
Use Tab Name in Lookup Field 3
how can I make a cell content equal the tab name 'sheet1' ? 12

Top