Multiple Workbook problem

[robgardner15]

I have this formula in a workbook but now I have to take the worksheet out and make it into its own workbook. I'm having trouble linking the new workbook BrianSummary.xlsx to the original workbook that holds on the data BrianFormulas.xlsx

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$B$4:$X$4"),B$4,INDIRECT("'"&list&"'!B$14:X$14")))

[brokenbiscuits]

When your indirect formula evaluates, it's basically looking for tab Jan through to July in the current workbook, which don't exist. You therefore need to include a reference to the original workbook within the formula.

In B4:

=IF(SUMPRODUCT(SUMIF(INDIRECT("'[BrianFormulas.xlsx]"&list&"'!$A$2"),$A$2,INDIRECT("'[BrianFormulas.xlsx]"&list&"'!b$4")))=0,"",(SUMPRODUCT(SUMIF(INDIRECT("'[BrianFormulas.xlsx]"&list&"'!$A$2"),$A$2,INDIRECT("'[BrianFormulas.xlsx]"&list&"'!b$4")))))

A further problem you have, however, is that (as far as I'm aware) the indirect formula won't work unless both workbooks are open, so it kinda makes splitting them into separate workbooks redundant. Off the top of my head, maybe the best way around this would be to collate all months of BrianFormulas onto one summary sheet, and then use a combination of OFFSET and MATCH to pull back the info based on cell A2.

[Roger Govier]

I have this formula in a workbook but now I have to take the worksheet out and make it into its own workbook. I'm having trouble linking the new workbook BrianSummary.xlsx to the original workbook that holds on the data BrianFormulas.xlsx

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$B$4:$X$4"),B$4,INDIRECT("'"&list&"'!B$14:X$14")))

Hi Rob

You shouldn't start another thread for the same problem.
I have answered your original thread.