Partial Path to worksheet from a cell (1 Viewer)

Hello,
I would like to modify the formula below to get the "Baker, Tom" part of the formula from a cell like "A1" and am not quite sure how to do it?

Row A contains the names of the other worksheets.


Thank you

I'm not sure that Excel supports that level of abstraction in the cells, but you can certainly query another spreadsheet through VBA.

I'd define a new Excel.Application, open the target workbook (pull the data from the spreadsheet's cells), and grab the range you're looking for (could also pull that data from the current spreadsheet's cells), then feed it into Application.WorksheetFunction.SumProduct.

Is there any reason you can't pull the data into another sheet of your current workbook via external data connection?

Thank you for your reply,

This particula formula is one of the few that can be read from one workbook while the other (target) is closed (as far as I know).

So the idea is to have a collection of files (target) read by a master file and as I receive an updated target file, I paste it over the existing one. This updates the master file.

The master workbook will have the name of the worksheet ("Tom Baker") in cell A2, but not the path to it. So I just need to use the value in cell A2 to help find the workseet for Tom Baker.

So something like (which doesn't work)...

I'll write up some VBA when I get home, unless someone else beats me to it.

Am I correct in assuming that you're running the SUMPRODUCT formula on one cell (F2)?

GSSDevelopment said:

I'll write up some VBA when I get home, unless someone else beats me to it.

Am I correct in assuming that you're running the SUMPRODUCT formula on one cell (F2)?

Click to expand...

I'm taking that formula and dragging it down in the master sheet, in the target the will be B2 through Z2 not F2 (like my sample had).

Is it not possible to just reference "A2" as I have described in the formula? I just feel if I can get that solved, it will do exactly as I need.

As I mentioned before; I don't think Excel supports that second level of abstraction when it comes to evaluating a cell, then evaluating the whole function above that with the value of the first. At least, not automatically.

If you are up for a bit of manual work, you can set the formula cell equal to
And when you've copied that down to row Z, copy, Paste Special > Value, then go to the top of the column and alternate F2-Enter-F2-Enter-F2-Enter, and so on...

However, this doesn't allow you dynamically update the values in the A column and have the results update, because once you do the copy/paste values, you lose the dynamic setup of referencing the A column.

If you want the full dynamic setup, I'd suggest a VBA solution.


If anybody else has some expertise with this 2-layer formula evaluation, please chime in

The only way to do it with formula is to use INDIRECT

=SUMPRODUCT(--(INDIRECT("'C:\Documents and Settings\MyProfile\Desktop\Meeting\Lists\["&A2&".xls]"&A2&"'!$F$2")))

The only problem unfortunately, though, is that the source workbook would need to be open for INDIRECT to work....

so then if that is the case, then you probably would not need the path...

=SUMPRODUCT(--(INDIRECT("'["&A2&".xls]"&A2&"'!$F$2")))