how to obtain sheet name?

[Khoshravan]

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.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

[Gary''s Student]

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


"Khoshravan" wrote:

> 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.
> --
> Rasoul Khoshravan Azar
> Civil Engineer, Osaka, Japan

[Khoshravan]

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


"Gary''s Student" wrote:

> 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
>
>
> "Khoshravan" wrote:
>
> > 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.
> > --
> > Rasoul Khoshravan Azar
> > Civil Engineer, Osaka, Japan

[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).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Khoshravan" wrote:
>
> > 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.
> > --
> > Rasoul Khoshravan Azar
> > Civil Engineer, Osaka, Japan

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


In article <[email protected]>,
LACA <[email protected]> wrote:

> 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?

[Khoshravan]

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


"David McRitchie" wrote:

> 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).
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Gary''s Student" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Khoshravan" wrote:
> >
> > > 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.
> > > --
> > > Rasoul Khoshravan Azar
> > > Civil Engineer, Osaka, Japan
>
>
>

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

"Khoshravan" <[email protected]> wrote
> 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.