PDA

View Full Version : referring to a worksheet name in a formula.



Simi
2012-03-04, 02:44 AM
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
2012-03-04, 09:11 AM
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 ...


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


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


=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