Excel 2007 : concatenate 2 cells

[rrcobetar]

Hi, ppl


In excel 2007, in a FILE A, I need to return the values from an external FILE B.
This is the instruction:
='\\server\share\[FILE B.XLSX]sheet!BL3/1000
If I put this in a cell then the result is ok.
But I need to create this instruction from 2 different cells like:
Cell 1 ='\\server\share\[FILE B.XLSX]sheet '
Cell 2 = !BL3/1000
..I get the string
Cell 3 ='\\server\share\[FILE B.XLSX]sheet!BL3/1000
But not in form of instruction/formula
I try to apply indirect like:
=indirect(cell 3)
but I only get a !HREF error…

[NBVC]

cell 3 should have: ''\\server\share\[FILE B.XLSX]sheet'!BL3 note the apostrophes...

and then you can have something like =INDIRECT(A3)/1000 where A3 contains the info.


you can't put the 1000 in cell 3 and indirectly reference.

Note also that INDIRECT does not work with closed reference workbooks.