Using character strings stored within cells within a formula in excel

Question

I want to create a formula which pulls data from a file Model Results Tool - website.xlsm into another file in a tab called Step 2 - stacked ROI. I have built a formula to do this:

=IF(AND(B$3<>"",'Step 1 - description of models'!$B5<>""),SUMIF('[Model Results Tool - website.xlsm]Step 13 - ROI'!$B$3:$GT$3,B$3,'[Model Results Tool - website.xlsm]Step 13 - ROI'!$B$9:$GT$9),"")

where the B$3<>"" check is simply making sure that there is a column header for this metric and the 'Step 1 - description of models'!$B5<>"" check is making sure that the Model Results Tool - website.xlsm model has been listed on the front sheet reference list. NB. this is a list of models included within the project. Their names and the number of them will change project to project so this seems like the most sensible approach.

What I want to be able to do is to replace the string Model Results Tool - website.xlsm in the formula with the cell from the reference list which I have created 'Step 1 - description of models'!$B5<>"" such that the cell name is explicitly contained within the formula but it resolves to the content of the cell, i.e. Model Results Tool - website.xlsm.

I realise that this is a bit wordy and may seem over-kill but the reports are generated and analysed across a group of analysts and therefore needs to have governance set up in such a way that formulas aren't changed but look-up / reference lists can be.

Here are screenshots - please note that all data contained is completely made up

Answer 1

I propose to add an additional column in Sheet "Step 2 - stacked ROI" and fill it with the Model names. E.g. A7 thus contains digital. Then the following formula will sum up your data from the desired sheet:

 =IF(AND(C$3<>"",'Step 1 - description of models'!$B5<>""),SUMIF(INDIRECT("'"&VLOOKUP($A7,'Step 1 - description of models'!$A$5:$B$31,2,FALSE)&"Step 13 - ROI'!$B$3:$GT$3"),C$3,INDIRECT("'"&VLOOKUP($A7,'Step 1 - description of models'!$A$5:$B$31,2,FALSE)&"Step 13 - ROI'!$B$9:$GT$9")))

The restriction that teylyn mentioned is of course true (only working for open workbooks), but the numbers will update and stay in the result sheet after you opened and closed all the source worksheets. Hope my hint of the formula helps you.

Answer 2

As I understand it, you want to store the name of a file in a cell, then use a reference to that cell in a formula that performs calculations on data from that file.

Excel's INDIRECT() function can be used to convert text to a cell reference. Unfortunately, INDIRECT() will not work if the text in the cell points to a closed workbook. Any file referenced by INDIRECT() must be open for the formula to work. In many cases this defeats the purpose of a dynamic file name.

There is a free add-in by Laurent Longre called Morefunc.xll, which has a function called INDIRECT.EXT(), which works with closed workbooks. Morefunc.xll can be downloaded here. Note that it does not work with 64 bit versions of Excel.