how to obtain sheet name?

[Guest]

I want to put sheet name as a referrence in "ifcount" function.
Is there any function to give the sheetname as a parameter, to be used in
functions (not only in "ifcount" but in others).
I know it is possible in VBA, but I don't think I can do it in VBA. I need
Excel.

 

[Guest]

Use the CELL() function:

=CELL("filename") will display:
C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
the full file name



=LEFT(A1,FIND("[",A1,1)-2) will display:
C:\Documents and Settings\Owner\My Documents
the path name


=MID(A1,FIND("[",A1,1)+1,FIND("]",A1,1)-FIND("[",A1,1)-1) will display:
cell function.xls
the workbook name


=RIGHT(A1,LEN(A1)-FIND("]",A1,1)) will display:
Sheet1
the sheet name

 

[Guest]

Very nice solution. It is fantastic.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

Use the CELL() function:

=CELL("filename") will display:
C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
the full file name



=LEFT(A1,FIND("[",A1,1)-2) will display:
C:\Documents and Settings\Owner\My Documents
the path name


=MID(A1,FIND("[",A1,1)+1,FIND("]",A1,1)-FIND("[",A1,1)-1) will display:
cell function.xls
the workbook name


=RIGHT(A1,LEN(A1)-FIND("]",A1,1)) will display:
Sheet1
the sheet name

--
Gary's Student

I want to put sheet name as a referrence in "ifcount" function.
Is there any function to give the sheetname as a parameter, to be used in
functions (not only in "ifcount" but in others).
I know it is possible in VBA, but I don't think I can do it in VBA. I need
Excel.

 

[David McRitchie]

Please stop posting with CELL("filename") as it is missing a reference cell
so will point to the active cell which could be in another worksheet or
another workbook. It does not matter what cell you use A1 is
fine. CELL("filename",A1)

Explained more on my page:
http://www.mvps.org/dmcritchie/excel/pathname.htm

To obtain the sheetname all in one formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Note: The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

Use the CELL() function:

=CELL("filename") will display:
C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
the full file name



=LEFT(A1,FIND("[",A1,1)-2) will display:
C:\Documents and Settings\Owner\My Documents
the path name


=MID(A1,FIND("[",A1,1)+1,FIND("]",A1,1)-FIND("[",A1,1)-1) will display:
cell function.xls
the workbook name


=RIGHT(A1,LEN(A1)-FIND("]",A1,1)) will display:
Sheet1
the sheet name

--
Gary's Student

I want to put sheet name as a referrence in "ifcount" function.
Is there any function to give the sheetname as a parameter, to be used in
functions (not only in "ifcount" but in others).
I know it is possible in VBA, but I don't think I can do it in VBA. I need
Excel.

 

[LACA]

-To obtain the sheetname all in one formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)-


What is the significance of 255? Is that the limit for number of
characters the formula will look at? Could I use 355 and get a
different result, if the string had that many characters?

 

[JE McGimpsey]

Sheet names can have only 31 characters in current versions of XL. 255
is just a large number to ensure that all characters in the sheet name
are captured. You could use 31 if you wanted, but future versions of XL
might not work.

My personal use of 255 comes from writing assembly language programming.
255 is (2^8 - 1), or the largest integer that can be stored in an 8-bit
byte.

 

[Guest]

Dear David
Thanks for your valuable comments. I always enjoy reading your site when I
am in trouble. so in cell function the reference is not optional (it is
better not to be optional) and ommited.
thanks
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

Please stop posting with CELL("filename") as it is missing a reference cell
so will point to the active cell which could be in another worksheet or
another workbook. It does not matter what cell you use A1 is
fine. CELL("filename",A1)

Explained more on my page:
http://www.mvps.org/dmcritchie/excel/pathname.htm

To obtain the sheetname all in one formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Note: The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

Use the CELL() function:

=CELL("filename") will display:
C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
the full file name



=LEFT(A1,FIND("[",A1,1)-2) will display:
C:\Documents and Settings\Owner\My Documents
the path name


=MID(A1,FIND("[",A1,1)+1,FIND("]",A1,1)-FIND("[",A1,1)-1) will display:
cell function.xls
the workbook name


=RIGHT(A1,LEN(A1)-FIND("]",A1,1)) will display:
Sheet1
the sheet name

--
Gary's Student

I want to put sheet name as a referrence in "ifcount" function.
Is there any function to give the sheetname as a parameter, to be used in
functions (not only in "ifcount" but in others).
I know it is possible in VBA, but I don't think I can do it in VBA. I need
Excel.

 

[David McRitchie]

Hi Rasoul,
It is optional, it just not what you want. I said active cell, I meant
last updated sheet, which was useful to me to get back to where
I had been, but I think that the crippled form might also be implicated
in ghosting problems, so I don't use it for that purpose either anymore..