referring to a worksheet name in a formula.

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
Can you refer to the name of a worksheet in a formula?
I have a work around this so far by just putting the name of the worksheet in cell A1 then referring to that cell,
but I don't want to have to code that all the time when my sheet names are the value I want.

Also I need help with a sum formula. I want to get a sum of cell A5 to (end of entered data).

Thank you,

Simi
 

Zack Barresse

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
Hello Simi,

To sum column A, you can just use SUM(A:A) and it will ignore text. If you want a dynamic range, you could use something like this ...

Code:
=SUM(A5:INDEX(A:A,MATCH(9.999E+307,A:A),1))

To get the name of the worksheet as a formula, you can use something like this ...

Code:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Or if you download and install ASAP Utilities you can use the built-in function...

Code:
=ASAPSHEETNAME()

There are other add-ins with this similar function because 1) it's not a native function, and 2) it's asked for quite often.

HTH
 
Top