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

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.

Yes there are several ways, but in order to help you with the solution, you have to provide your workbooks or dummy ones because it is too complicated to explain it, but by giving a sample dummy workbooks someone can implement it, then you can open it and ask questions how and maybe ask for steps to arrive at the solution, otherwise no one is going to explain it to you because its too time consuming and they have to recreate your problem based on a guess which could be incorrect and render the solution moot and useless.

Answer 2

Understandable. Try this: link here.

A1 in the 4th workbook is a slimmed down version. I think my notes in red explain it, but let me know if it's not clear. I would prefer a formula-based solution, but UDF or VBA is certainly fine.

Thank you.

Answer 3

The only way you can create references to files that are closed is by using VBA, or Power Query.

I don't like using VBA if a solution can be had using existing tools, so if your version of excel is 365 then I would use Power Query.

Screen Shot of files 1-3 queried and loaded into a sheet called References:

when you reference using OFFSET() function, it will yield a spill range which you will have to manipulate further to get the specific value based on possibly a label:

however if used with INDIRECT()+ADDRESS() functions, it returns a specific value:

So your solution would be:

Power Query + INDIRECT() + ADDRESS() functions

If you have any further questions on the matter, don't hesitate to reach out.

cheers

Answer 4

Thank you for the time you've spent on this.

This would need to be for Excel 2019 and before. Additionally, the needed solution is for cells in the middle of a dense financials-related sheet, thus no room for spill ranges or adding things to columns on either side. I'd prefer not to have to add additional sheets if possible.

My current way is to copy the formula from one cell into Word and then replace that cell reference with the next (e.g., $A$1 to $A$2), and then paste that back into Excel and repeat. While it gets the job done, there's got to be a better way.

What would a VBA solution be, if you don't mind? Would it function like the 'evaluate' UDF did and thus enable me to copy a formula down?

Answer 5

I only use functions that even though the result of a certain function in the earlier stage of the formula progression result "SPILLS", the final outcome of the formula in its entirety does not have a spilled range returned value. Functions that return a spilled ranges are helpful in dynamically filtering a range for a desired result, i.e.

types of data sets that involves date ranges such as this:

the only way to get the MAX(EndDate)-MIN(StartDate) for Person 1 is to use a formula that returns a spilled range and no other way in formula form except to physically query the data set and create a table that a formula can then be created from the resultant table.

So Functions with spilled ranges allow the user to create a formula based on ranges without having to query and create a physical table to derive its calculations from it.

So formula minus the physical table range but instead use a "Spilled Range" (virtual table)

VBA is not my forte, hopefully someone would come around to help you with a VBA solution.