Using sheet names as variables

[murugaraj]

We can use CELL function to retrieve various data about a cell and use them in formulas too. Is there a similar way to use sheet names?

I have a workbook with productivity of each employee in a single table (a table with 20 employee names with their daily productivity). There is another workbook with 20 worksheets, each sheet for an employee. The sheet is names after the employee name. I want to use the employee name by referring to the sheet name. Something like:

if(Name="name of this sheet", ..., ...). Is that possible? Is there any roundabout, if not?

Thanks for your insights.

Murugaraj

 

[Hui]

I generally use a macro to put sheet names into a known cell on each sheet

like A1

 

[murugaraj]

Oh, but I'm yet to learn macros. My present knowledge of macros is recording and editing them to a certain level. I think it's time I try that too. Thanks, Hui.

 

[Hui]

or you could use

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Make sure you save the worksheet before you use this

 

[kchiba]

You could use the fo;;owing function:


Function ShName(ByRef range As Variant)

ShName = range.Parent.Name

End Function


on the worksheet enter =ShName(A1)

 

[cyrilz]

If you have sheet names in one column (or at least something you can rely on to determine the name of the sheet) you can use the INDIRECT() function like this (assuming B3 is the sheet name :

= INDIRECT( "'" & B3 & "'!somecellref" )

This formula will give you the value of the "somecellref" cell.


Hope this helps too


Cyril Z.

 

[sparkyvega]

I use Hui's formula all the time! Works great. I even contatenate the filename with other info when needed.


-- sparkyvega

 

[murugaraj]

Hui,

I can't resist but wonder how smart your formula is. thanks a ton.

@cyrilz: your trick is good, too; but it is a little roundabout. Thanks for sharing.

Murugaraj