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

try defining a named range in the referenced workbook.

Answer 2

Does that work?

Answer 3

check this post out:

Re: Linking Entire Sheet to another Worksheet - Microsoft Tech Community