Build formula using field values as text in the formula referencing another workbook
 

I want to build an Excel formula using field values as text in the formula referencing another workbook.

From an open workbook, I want to use a formula like this in cell A1:
='[FileName.xlsx]SheetName'!$x$#
where SheetName is obtained from two different cells in the open workbook, and will change depending on the value of those two cells
AND
where $x$# is obtained from two cells in same open workbook and will change depending on the value of those two cells.

In simple terms I want to combine two cells together to form the SheetName and Cell Address in the external file based on different variables in the current workbook.

If cell B1 contains the value of 2012, and cell B2 contains the value of 12, then SheetName will equal B1 + B2 resulting in 201212
AND
if cell C1 contains the value of 'M, and cell C2 contains the value of 15, then $x$# will equal C1 + C2 resulting in $M$$15

The final formula in cell A1 would in the above example be:
='[FileName.xlsx]201212'!$M$15

Obviously changing values in cells B1, B2, C1 and C2 would change the formula in cell A1, resulting in pulling different worksheet/cell values from external file.

The issue is how to combine all these values into one cell that is a formula using some thing like CONCATENATE or INDIRECT maybe. I can get parts of it to work, but can't get a complete working formula.

Thanks in advance for any help. Kirk

Hi Kirk,

Set up as your example above, the following formula in cell A1 should do the trick.

=INDIRECT("'[filename.xlsx]"&B1&B2&"'!"&C1&C2)

This would assume the two files are in the same directory.

Hope that helps.

S.

It appears that the INDIRECT function only works when both files are open. I have tried to reference both filename.xlsx and the full address 'C:\dir\filename.xlsx. Both work as long as the file is open; as soon as you close the referenced file, and recalculate the worksheet with the formula, it returns a #REF! error.

I can't force users to open the other file, so I need a solution that will work when it is closed. Anyone know a way other than INDIRECT, or perhaps I am missing something in the way INDIRECT works?

Your original post didn't specify that the second file would be closed. Just that sort of information that makes the difference between getting a right answer and a wrong one!

I think you'll have to go down the route of "custom functions".
Have a quick search for "INDIRECT.EXT" for some further information. I've not tried it myself so cannot guarantee it will work for your particular conundrum....

Spencer,

Yes I try to give complete info, but didn't try closing the file until after I got it to work. I found the INDIRECT.EXT func and it works like a champ. Thanks for your help!!

Not a problem Kirk. Glad you got it fixed :)

Hey can I ask how you got this to work using the 'INDIRECT.EXE' function as I have very similar problem as you did above...