D
Dave Peterson
You may want to add
application.volatile
to your function.
It still may be one calculation behind, though.
application.volatile
to your function.
It still may be one calculation behind, though.
Dave,
Yes I do write the formulas as I need them... however for sheetnames I
prefer to use the following tiny little VBA code:
Function qdSheetName() As String
qdSheetName = Application.Caller.Parent.Name
End Function
Not because it works better, but just because it is much clearer for the
user to see what a function like =qdSheetName() might do
than guessing what the long formula below would do...
Kind regards,
Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
Dave Peterson said:Yep. You're correct.
Do you do it more than once????
(I might save it for future use if I really needed it!)
Dave, Alec,
The function you mention does return the sheetname, not the filename.
To return the filename you can use a similar approach though:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-1
Kind regards,
Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
P.S.: Yes Dave, I do code these myself.
"Dave Peterson" <[email protected]> schreef in bericht
I missed a step in my example of how to see the difference. Make sure
that you
have two different worksheets visible when you do it. And then calculate
(F9)
the workbook. And inspect the results of those two cells. You'll see the
difference.
I'm not sure about others, but I've never typed this formula myself (from
scratch):
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
But I have kept a few posts that showed it. And I have a link to Debra
Dalgleish's site. She has it here:
http://www.contextures.com/xlfaqFun.html#SheetName
Shiperton Henethe wrote:
Crud!
I get exactly the same result whichever version I use.
(Excel2002/Win2K)
e.g. "D:\Docs\[TIMESHEET.xls]Summary"
As the previous poster said earlier in this thread
you have to strip out the directory and worksheet info
manually - which is rather messy to code.
What a friggin nightmare!
Thanks Micro$oft. (Not)
Ship
Shiperton Henethe
Just so you can see it:
Create a new workbook with 2 sheets.
Save it.
Window|new window
window|arrange horizontal
Put the formula
=cell("filename")
in A1 of each sheet.
Try it with
=cell("filename",a1)
You'll see the difference.
RWN wrote:
Dave;
I'm probably over my head here, but I don't understand the cell
reference.
When I use =Cell("filename") I get the path, workbook & worksheet
name.
I'd appreciate it if you would enlighten me on what the reference
protects you from (I've found that a little bit of knowledge is
indeed
dangerous).
--
Regards;
Rob
------------------------------------------------------------------------
it's probably best to add a cell reference to that formula:
=cell("filename",a1)
Otherwise, you might not get what you expect.
RWN wrote:
You can (in Xl97 & 2000, at least) display the filename using
=Cell("Filename") (include the quotes around "Filename")
--
Regards;
Rob
----------------------------------------------------------------------
--
Incredibly complicated but yes this does seems to work!
With thanks.
Ship
P.S. The *reason* why I wanted this is
it is that I want to be able to have my printouts say very
distretely
what
the file
name is: i.e. it's there but you have to look for it!
(So I've formatted it to size=6 and colour=mid grey!)
I strongly request that in future microsoft allow
the headers and footer to be formatted - and that
would save us all a lot of hassle!
It would also be nice to have a filename() function!
<end>
One way
=LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255),FIND(
".",
MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,255))+3)
can be put in any cell
if you want the full path
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[
",""
)
--
Regards,
Peo Sjoblom
Hi
Anyone know of any msExcel function that will
give the name of the current file?
To be honest I want if for printout purposes
because I am trying to avoid the custom header
etc (which seems difficult/impossible to format)
Shiphen
Shiperton Henethe