Using Indirect Across Multiple Closed Workbooks

**robgardner15** · 07-08-2011, 03:47 AM

I created a formula to copy cells that matched criteria from one cell to another.

=IF(SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A$2"),$A$2,INDIRECT("'"&list&"'!b$4")))=0,"",(SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A$2"),$A$2,INDIRECT("'"&list&"'!b$4")))))

This is telling excel to look at cell A2 in the worksheet "Summary" and match it with an identical value found in worksheets Jan:July (group named "list"), then when a match is found copy cells B4:W4 to 'Summary' B4:W4

This works perfectly but now i have to remove the worksheet Summary and make it into its own WorkBook. I can't figure out how to link the function to my original workbook

Original WorkBook is BrianFormulas (i left summary sheet in it just so you guys can see what is going on) The workbook that needs help though is BrianSummary

Thank you in advance for any help you can provide

**Roger Govier** · 07-08-2011, 05:09 AM

Hi

If you can accept a VBA solution, then you could leave Brian Formulas with it's summary sheet still in, and then just have the Summary Sheet in Brian Summary, refer to the equivalent cells.

e.g.
B4 ='[BrianFormulas.xlsx]Summary'!B$4
B6 ='[BrianFormulas.xlsx]Summary'!B$6

then have event code on sheet Summary of the Summary file something like

Please Login or Register  to view this content.

You may need to add more error checking to the code, but it seems to work OK.
It assumes that both files reside in the same folder.

I have attached Brian Summary.xlsm - just use the dropdown in cell A2 to select the month required