Originally Posted by solardirect

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