Subscribe
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
Using sheet names as variables
(8 posts) (5 voices)-
Posted 2 years ago #
-
I generally use a macro to put sheet names into a known cell on each sheet
like A1Posted 2 years ago # -
or you could use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Make sure you save the worksheet before you use thisPosted 2 years ago # -
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.
Posted 2 years ago # -
I use Hui's formula all the time! Works great. I even contatenate the filename with other info when needed.
-- sparkyvega
Posted 2 years ago #
Reply
You must log in to post.
Welcome to Chandoo.org. New here?
Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.
