what's wrong w/ this formula?

[zazzz]

i want to import the name of my file into my cell. by looking at help i've been able to do that but dont really undestand the formula properly, which will make it hard to modify.

here's me reverse engineering it. can you please explain why this isn't working, i've getting a #value error.

=MID( CELL("[hello]"), 2, 5)


in the original formula (which works) i used cell("filename") which worked fine. but when im evaluating that formula, rather than it showing up as "Hello.xls" it shows up as "c\doc and set \ excel \ journal \ blah blah blah blah \ ... \. ..... \ etc etc \ Hello.xls" which makes it REALLY hard to decode (esp b/c this path is referred to 3 times in the same code). is there a way to go straight for the name w/o the path?

[Bob Phillips]

Try this

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1)),255)

[NBVC]

According to Excel help on CELL() function, using "filename" gets you the whole path... that is why the MID(CELL(.....)) formula exists... to reduce it down to only the file name or sheet name.

[Marcol]

One way

Please Login or Register  to view this content.

Remember that the workbook has to be a saved workbook for this to work.

[zazzz]

thanks a lot for the help fellows. unfortunately you haven't actually answered my question

i already have the formula i need. that was given in the help files. im just trying to understand it now, and in order to do that i must play w/ it. so i need to understand what's wrong w/ my modified formula:

=MID( CELL("[hello]"), 2, 5)



i also want to know if there's any way to reference the file name, so that when im editing the formula i only see the file name, not the path.

According to Excel help on CELL() function, using "filename" gets you the whole path... that is why the MID(CELL(.....)) formula exists... to reduce it down to only the file name or sheet name.

if im reading this right there's no way to only see the file name without the path in the formula bar. is this correct? i really hope not.

[NBVC]

You can't use CELL("[hello]") if you read the help files, you must use one of the descriptions shown in the Info_type column.

http://office.microsoft.com/en-us/ex...010062392.aspx

if you want to pull Hello from that, it is simply:

=MID("[hello]", 2, 5)

[zazzz]

You can't use CELL("[hello]") if you read the help files, you must use one of the descriptions shown in the Info_type column.

http://office.microsoft.com/en-us/ex...010062392.aspx

if you want to pull Hello from that, it is simply:

=MID("[hello]", 2, 5)

ahh OK that makes sense.

can you advise on the second part of the question?

>i also want to know if there's any way to reference the file name, so that when im editing
>the formula i only see the file name, not the path.

[NBVC]

There isn't... the CELL("format",....) function will display the whole path and there is no alternative.... again, that is why the big formula to extract the filename exists.

[tlafferty]

Here's an elegant little user defined function that is much clearer and easy to use:

Please Login or Register  to view this content.

To use it, paste the code into a code module, then enter a formula like this anywhere in your book:

Please Login or Register  to view this content.

To get the sheet name:

Please Login or Register  to view this content.

[Bob Phillips]

Here's an elegant little user defined function that is much clearer and easy to use:

Except that for the sheet option it doesn't update if the sheet name changes, and if you are on another sheet and do a full recalc, it will then change to that active sheet name.

[zazzz]

thanks guys for all the help here.