Convert Text String (with Reference to Another Workbook) to Formula

Question

Is there a way to take a text string with a reference to another workbook and convert it to formula? I tried all the options using UDFs that come up online but couldn't get them to work.

I'm needing to sum cells from 3 different workbooks, but in a 4th workbook, and with the other workbooks closed. I've got to copy the formula down for multiple cells (e.g., A1-A20), with each summing 3 different workbooks (all 3 A1s, then all 3 A2s, etc. in this 4th workbook). I want to provide the 3 workbook references in, for example, C1, C2, and C3, and then just refer to those and add the relative reference "ADDRESS(ROW(),COLUMN())" after it.

So A1 in the 4th workbook would point to C1 for the 1st workbook reference and combine with "ADDRESS(ROW(),COLUMN())" to provide the cell address ending ("A1"), and so forth for C2, etc.

Hope that makes sense.

Answer 1

Roger that. Thank you again.

Answer 2

How about this - I changed the setup slightly so that you can more easily change the parameters.

Sub CreateFormulas()
Dim strFormula As String
Range("A:A").ClearContents
strFormula = "='" & Range("D1").Value & "[" & Range("D2").Value & "]" & Range("D5").Value & "'!A1" & _
"+'" & Range("D1").Value & "[" & Range("D3").Value & "]" & Range("D5").Value & "'!A1" & _
"+'" & Range("D1").Value & "[" & Range("D4").Value & "]" & Range("D5").Value & "'!A1"
Range("A1:A" & Range("D6").Value).Formula = strFormula
End Sub

See sample workbook (The formulas currently evaluate to #REF! because the path does not exist on my computer. Press F9 to recalculate)

Answer 3

Thank you, Hans! This is interesting. Is there a way to have it be something I could just copy into the relevant cells and it work similarly? This works great for the example of A1:A20, but I've got a dense financials-related sheet and the target cells skip rows, are in different columns, and sometimes in different sheets. Thank you again.

Answer 4

If you want to use formulas only, you'd need INDIRECT, but as has already been remarked, INDIRECT does not work with closed workbooks...

Answer 5

Thank you. Do you know of a UDF option?