Formula to extract file name not working correctly

[sparky2205]

Hi folks,
I have the following formula in a template:

=MID(@CELL("filename"),SEARCH("[",@CELL("filename"))+1, SEARCH("(",@CELL("filename"))-SEARCH("[",@CELL("filename"))-2)

Each time the template is used it is given a sequential number by the system.
I download two of these documents to a network folder, which are now called, let's say, File1 and File2.
I open the two documents in the order File1, File2.
Now they both have the document number File2 in the field where the formula is.
If I open them in the order File2, File1 they both have the number File1 where the formula is.
i.e. whichever document is opened last both documents will take that document number.
It doesn't matter how many documents you have open all documents will take the number of the last document opened.
I feel like I'm missing something simple but I don't know what.

 

[Anthony47]

Dont't use CELL("filename") but

CELL("filename",A1)

 

[sparky2205]

Thanks Anthony,
that works. Could you explain that logic to me please?
What does using A1 as the [reference] actually do?
And why does it not work without it?

 

[Dave Patton]

Your formula did not work for me.
The following will yield the file name.
You may have to make the formula volatile.

T202207b.xlsm
	A
3	T202207b
4	D:\Excel14\[T202207b.xlsm]1a
5
1a

Cell Formulas
Range	Formula
A3	A3	=LET(FileN,CELL("filename",A1),REPLACE(LEFT(FileN,FIND(".",FileN)-1),1,FIND("[",FileN),""))
A4	A4	=IF(NOW()>N1,CELL("filename",A1),"")

 

[Anthony47]

Thanks Anthony,
that works. Could you explain that logic to me please?
What does using A1 as the [reference] actually do?
And why does it not work without it?

Without a reference the function will return a value based on the current active cell
See CELL function

 

[sparky2205]

I'm not sure why it doesn't work for you Anthony, but it does work for me.
Thanks very much for the pointer to the CELL function description.
It now makes perfect sense.

Thanks for your help.

 

[Anthony47]

Thank you for the feedback

I'm not sure why it doesn't work for you Anthony

You meant Dave, I guess...

 

[Dave Patton]

T202207b.xlsm
	A
2	#VALUE!
3	T202207b
1a

Cell Formulas
Range	Formula
A2	A2	=MID(CELL("filename"),SEARCH("[",CELL("filename")+1), SEARCH("(",CELL("filename"))-SEARCH("[",CELL("filename"))-2)
A3	A3	=LET(FileN,CELL("filename",A1),REPLACE(LEFT(FileN,FIND(".",FileN)-1),1,FIND("[",FileN),""))

 

[sparky2205]

You meant Dave, I guess...

Apologies Anthony, yes I did mean Dave for that one.

 

[Dave Patton]

sparky2205 or​

Anthony47​

Did either of you actually try the formula that

sparky2205 posted?​

I showed with my post that it gives a Value error. Did the post not show the complete formula?

Could you post with XL2BB an example where it works?