Displaying Sheet Tabs Names in Cells

  • Thread starter Thread starter Cardin Smith
  • Start date Start date
C

Cardin Smith

Can anyone tell me if there is a way for me to display the Name of a Sheet
Tab in a cell.

Take for example I have 4 worksheets, labelled Shawn, Kevin, Mary, & Data.
In data, I want it to show the name of the worksheets. So in Cell A4 I
would like it to say Shawn, then in B4 I can put Shawn's data in. Is there
a function or a formula that allows me to do this.

Thanks for any help.
 
Hi

Maybe this UDF will do for you.
------
Public Function TabI(TabIndex As Integer) As String
Application.Volatile
TabI = Sheets(TabIndex).Name
End Function
------

Insert the function into worksheets module.
Into cell A4 enter the formula
=IF(ISERROR(TABI(ROW()-(ROW($A$4))),"",TABI(ROW()-(ROW($A$4)))
and copy it down - you get the list of your woksheets.
 
Thank you for your help. I have tried this but am getting an error message
when i try to insert the function into the worksheet.

Just to ensure i am doing this correctly. The first part of the solution, i
should place into the Visual Basic Editor.

Then in my worksheet i should place the information. I think what i am
querying though, is the amount of brackets used in the forumla.

Thanks for your help, it's brought me alot closer to understanding what is
needed.
 
Awh, i believe i have gotten it to work, it was a bracket which was missing.

=IF(ISERROR(TABI(ROW()-(ROW($A$4)))),"",TABI(ROW()-(ROW($A$4)))
)
Seemed to work for me

Can i ask you the significance of the $A$4 in the formula?
 
Hi

Open the workbook
Press Alt+F11 - VBA editor activates.
In VBA-Project window (leftmost upper one) look for your VBA-Project (it has
your workbook name in brackets). Check, has it some module, and when not,
then insert one (Insert.Module from menu) - by default it will be named as
Module1.
Double-click on project's Module object. The module is activated in right
window.
Copy the function code into module. Close the VBA editor.

When you did all properly, then the new UDF (User Defined Function) will be
available in Paste Function wizard (the one you activate clicking on 'Insert
Function' icon on Standard toolbar) under categorie 'User Defined '. When
not, then you have done something wrong.
 
Hi


Cardin Smith said:
Awh, i believe i have gotten it to work, it was a bracket which was missing.

=IF(ISERROR(TABI(ROW()-(ROW($A$4)))),"",TABI(ROW()-(ROW($A$4)))
)
Seemed to work for me

Can i ask you the significance of the $A$4 in the formula?
Click to expand...

When rows are added/deleted on sheet, so long the row 4 is not deleted, the
formula adjusts automatically. And oops, it must be
.... TABI(ROW()-ROW($A$4)+1) ...

In row 4, the expression above returns 1, in row 5 it returns 2, etc. When
p.e. you insert a new row (p.e. 2:2), the first formula will be in cell A5,
the formula will be
=IF(ISERROR(TABI(ROW()-(ROW($A$5)+1)),"",TABI(ROW()-ROW($A$5)+1))
and it returns still the name of first sheet. When you had had the formula
=IF(ISERROR(TABI(ROW()-3)),"",TABI(ROW()-3))
instead, then after a row would be added in manner as in example above, your
list of sheet names would start from sheet number 2 - the first sheet would
be omitted.
 
Back
Top