Excel 2007 : Issue with =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

**Damien_17** · 06-17-2010, 05:53 PM

Dear All,

I searched the forum looking for a thread that might solve my problem but didn't see anything about this particular issue.

I use =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) to "extract" the Sheet name into a cell from the same sheet.
It used to work for a while but now, for some unknown reasons, I get on each and every sheet the value of the last sheet I entered that formula onto.

i.e.
I have Sheet1, Sheet2, Sheet3, etc... in my workbook.
the formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) is in A1 on each and every sheet.

So on Sheet1, I should get "Sheet1" in the A1 cell.
On Sheet2, I should get "Sheet2" on the A1 cell.
etc...
Well, if I create a new sheet called Sheet9999, i get "Sheet9999" as a value on A1 (make sense) BUT in all the sheets, not just the new one.
If I now got to Sheet1, re-enter the formula in A1, I get "Sheet1" as the value on all the sheets...

Could you shed some light on that issue?

Thank you all,

Damien

**daddylonglegs** · 06-17-2010, 06:01 PM

Hello Damien, you need to include a reference in the CELL function (any cell reference) then the sheet name will always be for the sheet on which the formula resides, e.g.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

**Damien_17** · 06-17-2010, 06:04 PM

I just realize something...
If i use (in Red, the modification from the formula posted above) =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) or in that matter an other cell name, it works just fine.
Why a location, actually any location, of a cell in CELL("filename",XX) is needed?

**Damien_17** · 06-17-2010, 06:05 PM

Originally Posted by daddylonglegs

Hello Damien, you need to include a reference in the CELL function (any cell reference) then the sheet name will always be for the sheet on which the formula resides, e.g.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Daddylonglegs,

Great timing... I saw your post when I submitted mine.
Thank you!

Damien

**Marcol** · 06-17-2010, 06:15 PM

Bad timing!!!

Please Login or Register  to view this content.

This will only work if the workbook has been saved, it will not work on a new blank workbook

[EDIT]
Daddylonglegs is bang on, (as usual)

This still stands as far as I know

This will only work if the workbook has been saved, it will not work on a new blank workbook

**daddylonglegs** · 06-17-2010, 06:15 PM

Originally Posted by Damien_17

Why a location, actually any location, of a cell in CELL("filename",XX) is needed?

Sometimes the Help files contain all the information you need. Help on CELL function says

"Reference is the cell that you want information about. If omitted, information specified in info_type is returned for the last cell that was changed"

CELL has many potential uses, some relating to individual cells, here, of course the actual cell used is not relevant but if you don't use it the above applies

**Damien_17** · 06-17-2010, 07:43 PM

my bad... should I checked the Help files first on this one.
Thank you DaddyLongLegs !

**StarGypsy** · 02-17-2021, 02:56 PM

Marcol & DaddyLongLegs -- Oh my gosh, I never knew this detai (this only works if the worksheet has been saved with a name). 10 years later, your reply is still helpful

Thank you so much - glad it was a simple solution.

**Khurram.ggt** · 03-09-2021, 10:12 PM

Question
Please help me
If a1 less then 0 then a1 minus b1 if b1 value
0 then c1 minus b1 please answer it

**FlameRetired** · 03-09-2021, 11:54 PM

Khurram.ggt Welcome to the forum.

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Additionally:
This thread is nearly 11 years old. It's not likely the original participants are monitoring it.