Sumproduct to closed workbook, multiple sheets question

[Long Nose]

Hi all,

I have a simple formula right now, but it is still developing.

=SUMPRODUCT(--([DRG_Mar_2010_Cont_030303.xlsx]Rb!$BW$5:$CH$5=Formula_Month),[DRG_Mar_2010_Cont_030303.xlsx]Rb!$BW$337:$CH$337)

My question is this, the formula will link to a specific sheet on the closed external source file and this formula will be repeated for several other source sheets, like Rc, Tg, Yh, Ra, and Rx, can I use the same formula and have the Rb (name of sheet) linked to a cell where if I change it, it will go to the new sheet?

This would allow me to keep the same formula and change only a plug cell to redirect my formula to a different but identical layout sheet.

I was thinking a sheetlist could somehow help me. Let me know if this is possible.

 

[jbeaucaire]

What you're talking about is called INDIRECT() referencing. Using built in native functions, you cannot indirectly reference closed workbooks.

But there are many addins you can use to give your workbook this capability. Only problem is that everyone who will use these workbooks will need to install the same adding. Search for MOREFUNC addin.