Cocatenation returns text string not formula result

[Carty]

Hi
In a worksheet I am trying to generate data using a formula that points to different workbooks on a network by concatenating text and cell values. The original formula looks like this:
code 1

=’\\Network address\customer\year\[order]worksheet’!cell reference

This works perfectly but the year, order and worksheet are variable. These values are defined in particular cells in the worksheet. Ideally I would write the formula as
code 2

='\\Network address\folder\customer\$B$1\[$C$1]$D$1’!cell reference

Secondly the order number is made up of letters and numbers but only the numbers are in cell C1 so the formula guidance would be
code 3

='\\Network address\folder\customer\$B$1\[text$C$1text]$D$1’!cell reference

I have managed to be able to arrive at the following:
code 4

=concatenate(“=‘\\Network address\folder\customer\”,$B$1,”\[text”,$C$1,”text]”,$D$1,”’!cell reference”

This just prints the formula in the cell rather than the outcome of the formula i.e.:
=‘\\Network address\folder\customer\$B$1\[text$C$1text]$D$1’!cell reference appears in the cell

Things I have tried;
The cell is formatted to General.
Using & rather than concatenate returns the same issue.
Using INDIRECT doesn't help - it returns an invalid cell reference ref error
code 5

=indirect(concatenate(etc, etc))

I don't have the ability to use EVAL (addin not added in! Not something I'm allowed to change)

Thank you

Paul

 

[RoryA]

Unless using code is ok, or you can ensure the source workbook is open, you're out of luck. If the source will be open, you can use INDIRECT.

 

[Carty]

Thanks Rory

Opened the file and INDIRECT worked. Interestingly the value remained when the source file was closed.

Off to do some VBA then

Paul