Excel cuts off part of formula

[Thibaut Br]

I am working in an excel file named "Stuurprogramma.xlsm"

I am allowing other documents to copy the formula of a certain cell, yet when I try to enter a cell adress in the document
it cuts off the file-specific part of the adres so other files start to look in their own file instead of in the 'mother' file, example underneath:

Input before pressing enter: '[Stuurprogramma.xlsm]Sheet1'!$B$12

Output after entering: Sheet1!$B$12

Can I turn off a setting that does this?
Thanks in advance.

[JLGWhiz]

If Stuurprogramma.xlsm is in a different directory than the default directory, then the path must be included.
example

Please Login or Register  to view this content.

Also noticed you did not use the = symbol in your post. I assuem it is used on the worksheet when entering the formula.

[Glenn Kennedy]

or, even more simply...

='[Stuurprogramma.xlsm]Sheet1'!$B$12

[Thibaut Br]

I don't think you understand the issue, I know I have to write down the file name when entering it into another document. The problem is that it vanished when I tried to enter the formula since Excel kept deleted the file directory part since it was about the file in which I entered the formula. The formula: INDIRECT("[Stuurprogramma.xlsm]"&"Sheet1!$B$12") did the trick for me. It didnt remove the file directory when pressing enter. Thanks anyway!

[Glenn Kennedy]

Maybe you didn't describe it very well!! Incidentally, INDIRECT will only work as long as the source file is OPEN.

What happens when you copy cell B12 from Stuurprogramma.xlsm and paste/paste special/paste link??

[Thibaut Br]

Ok so, the problem was: when I wrote "='[Stuurprogramma.xlsm]Sheet1'!$B$12" in the Excel formula tab in the file "Stuurptogramma.xlsm", it deemed the file location that I wrote into the formula tab to be unnecessary. So when I pressed enter to activate the formula, it converted it to "=Sheet1!$B$12"" which worked fine into the file "stuurprogramma" but once I used a macro to copy that cell content to another file , the new file ofcourse did not know in what file the cell B12 was located. That was my problem.

I am approaching the macro from another point of view so the issue that I had no longer bothers me, I now don't work via a controlling file (this was "stuurprogramma.xlsm"). Anyway, thanks for your input! I am new to cross feeding information with macro's (new to macro's in general aswell) so my initial approach was apparently not the right way to go.