Using "indirect" and "concatenate" to reference across workbooks.

[taketwo]

I am attempting to use "concatenate" and "indirect" to reference a cell in a different workbook, but am not having any luck.

In this example, an employee is interested in looking at job "999.123", and thus enters that number into the first cell. The file itself is saved as "999.123.xlsx", so when they enter "999.123" into the first cell, it should combine with the beginning and ending of the file-path listed - I can get it to link when I copy & paste the concatenated value into another cell, but I can't get it to work on its own.

Thanks in advance.

[teylyn]

Hi,

use something like

=INDIRECT(C3&C2&C4)

You don't need to start C3 with =
The indirect function already starts with the required = sign. But you may want to experiment with an additional ' at the beginning of C3.
Also, while you're testing, try the formula with the target workbook open. This may yield better results than if it is closed.

Use the Evaluate Formula functionality to see how your string gets put together step by step and is finally converted to a reference. This will help find errors.

cheers

[taketwo]

Thanks for the reply, but it's still not working - I've been trying to get this for over a month, but I just can't figure it out.

Thanks for trying, though.

[DonkeyOte]

Also, while you're testing, try the formula with the target workbook open. This may yield better results than if it is closed.

just to clear INDIRECT will only work with open targets, for closed targets see Laurent Longre's morefunc.xll add-in and specifically the INDIRECT.EXT function.

[NBVC]

It probably should be:

=INDIRECT("'\\Rehabserver\Company Shared Documents\ACT\Document\1 - PROJECT SUMMARIES\Reports\2010 Active\["&C2&".xlsx]Summary'!$F$16")

but note... this only works with open workbooks..

you will need an outside method such as using Morefunc.xll addin and INDIRECT.EXT to get it to work with closed workbook.

Morefunc.xll