software

I want to put sheet name as a referrence in quot;ifcountquot; function.
Is there any function to give the sheetname as a parameter, to be used in
functions (not only in quot;ifcountquot; 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

Use the CELL() function:

=CELL(quot;filenamequot;) will display:
C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
the full file name
=LEFT(A1,FIND(quot;[quot;,A1,1)-2) will display:
C:\Documents and Settings\Owner\My Documents
the path name=MID(A1,FIND(quot;[quot;,A1,1) 1,FIND(quot;]quot;,A1,1)-FIND(quot;[quot;,A1,1)-1) will display:
cell function.xls
the workbook name=RIGHT(A1,LEN(A1)-FIND(quot;]quot;,A1,1)) will display:
Sheet1
the sheet name

--
Gary's Studentquot;Khoshravanquot; wrote:

gt; I want to put sheet name as a referrence in quot;ifcountquot; function.
gt; Is there any function to give the sheetname as a parameter, to be used in
gt; functions (not only in quot;ifcountquot; but in others).
gt; I know it is possible in VBA, but I don't think I can do it in VBA. I need
gt; Excel.
gt; --
gt; Rasoul Khoshravan Azar
gt; Civil Engineer, Osaka, Japan

Very nice solution. It is fantastic.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japanquot;Gary''s Studentquot; wrote:

gt; Use the CELL() function:
gt;
gt; =CELL(quot;filenamequot;) will display:
gt; C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
gt; the full file name
gt;
gt;
gt;
gt; =LEFT(A1,FIND(quot;[quot;,A1,1)-2) will display:
gt; C:\Documents and Settings\Owner\My Documents
gt; the path name
gt;
gt;
gt; =MID(A1,FIND(quot;[quot;,A1,1) 1,FIND(quot;]quot;,A1,1)-FIND(quot;[quot;,A1,1)-1) will display:
gt; cell function.xls
gt; the workbook name
gt;
gt;
gt; =RIGHT(A1,LEN(A1)-FIND(quot;]quot;,A1,1)) will display:
gt; Sheet1
gt; the sheet name
gt;
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Khoshravanquot; wrote:
gt;
gt; gt; I want to put sheet name as a referrence in quot;ifcountquot; function.
gt; gt; Is there any function to give the sheetname as a parameter, to be used in
gt; gt; functions (not only in quot;ifcountquot; but in others).
gt; gt; I know it is possible in VBA, but I don't think I can do it in VBA. I need
gt; gt; Excel.
gt; gt; --
gt; gt; Rasoul Khoshravan Azar
gt; gt; Civil Engineer, Osaka, Japan

Please stop posting with CELL(quot;filenamequot;) 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(quot;filenamequot;,A1)

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

To obtain the sheetname all in one formula:
=MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,255)

Note: The CELL formulas with quot;filenamequot; will not work until the file has been saved (#VALUE! error).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Gary''s Studentquot; gt; wrote in message
...
gt; Use the CELL() function:
gt;
gt; =CELL(quot;filenamequot;) will display:
gt; C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
gt; the full file name
gt;
gt;
gt;
gt; =LEFT(A1,FIND(quot;[quot;,A1,1)-2) will display:
gt; C:\Documents and Settings\Owner\My Documents
gt; the path name
gt;
gt;
gt; =MID(A1,FIND(quot;[quot;,A1,1) 1,FIND(quot;]quot;,A1,1)-FIND(quot;[quot;,A1,1)-1) will display:
gt; cell function.xls
gt; the workbook name
gt;
gt;
gt; =RIGHT(A1,LEN(A1)-FIND(quot;]quot;,A1,1)) will display:
gt; Sheet1
gt; the sheet name
gt;
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Khoshravanquot; wrote:
gt;
gt; gt; I want to put sheet name as a referrence in quot;ifcountquot; function.
gt; gt; Is there any function to give the sheetname as a parameter, to be used in
gt; gt; functions (not only in quot;ifcountquot; but in others).
gt; gt; I know it is possible in VBA, but I don't think I can do it in VBA. I need
gt; gt; Excel.
gt; gt; --
gt; gt; Rasoul Khoshravan Azar
gt; gt; Civil Engineer, Osaka, Japan

-To obtain the sheetname all in one formula:
=MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,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?--
LACA
------------------------------------------------------------------------
LACA's Profile: www.excelforum.com/member.php...oamp;userid=30381
View this thread: www.excelforum.com/showthread...hreadid=544020Sheet 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 gt;,
LACA gt; wrote:

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

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, Japanquot;David McRitchiequot; wrote:

gt; Please stop posting with CELL(quot;filenamequot;) as it is missing a reference cell
gt; so will point to the active cell which could be in another worksheet or
gt; another workbook. It does not matter what cell you use A1 is
gt; fine. CELL(quot;filenamequot;,A1)
gt;
gt; Explained more on my page:
gt; www.mvps.org/dmcritchie/excel/pathname.htm
gt;
gt; To obtain the sheetname all in one formula:
gt; =MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,255)
gt;
gt; Note: The CELL formulas with quot;filenamequot; will not work until the file has been saved (#VALUE! error).
gt; ---
gt; HTH,
gt; David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
gt; My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
gt; Search Page: www.mvps.org/dmcritchie/excel/search.htm
gt;
gt; quot;Gary''s Studentquot; gt; wrote in message
gt; ...
gt; gt; Use the CELL() function:
gt; gt;
gt; gt; =CELL(quot;filenamequot;) will display:
gt; gt; C:\Documents and Settings\Owner\My Documents\[cell function.xls]Sheet1
gt; gt; the full file name
gt; gt;
gt; gt;
gt; gt;
gt; gt; =LEFT(A1,FIND(quot;[quot;,A1,1)-2) will display:
gt; gt; C:\Documents and Settings\Owner\My Documents
gt; gt; the path name
gt; gt;
gt; gt;
gt; gt; =MID(A1,FIND(quot;[quot;,A1,1) 1,FIND(quot;]quot;,A1,1)-FIND(quot;[quot;,A1,1)-1) will display:
gt; gt; cell function.xls
gt; gt; the workbook name
gt; gt;
gt; gt;
gt; gt; =RIGHT(A1,LEN(A1)-FIND(quot;]quot;,A1,1)) will display:
gt; gt; Sheet1
gt; gt; the sheet name
gt; gt;
gt; gt; --
gt; gt; Gary's Student
gt; gt;
gt; gt;
gt; gt; quot;Khoshravanquot; wrote:
gt; gt;
gt; gt; gt; I want to put sheet name as a referrence in quot;ifcountquot; function.
gt; gt; gt; Is there any function to give the sheetname as a parameter, to be used in
gt; gt; gt; functions (not only in quot;ifcountquot; but in others).
gt; gt; gt; I know it is possible in VBA, but I don't think I can do it in VBA. I need
gt; gt; gt; Excel.
gt; gt; gt; --
gt; gt; gt; Rasoul Khoshravan Azar
gt; gt; gt; Civil Engineer, Osaka, Japan
gt;
gt;
gt;

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

quot;Khoshravanquot; gt; wrote
gt; Thanks for your valuable comments. I always enjoy reading your site when I
gt; am in trouble. so in cell function the reference is not optional (it is
gt; better not to be optional) and ommited.