Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
-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? -- LACA ------------------------------------------------------------------------ LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381 View this thread: http://www.excelforum.com/showthread...hreadid=544020 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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 , LACA 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to obtain sheet name?
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" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |