refering cell formula to worksheet name as a variable

[emjaye]

At work we log various events & their duration for different staff usin
a worksheet per month, the "stats" throughout the year are the
compiled on another sheet,

I'm using a SUMPRODUCT formula, looking up data across the differen
sheets however, because there are 12 month sheets, I have 12 differen
lines of formula - 1 refering to each sheet by name i.e
=sumproduct((JANUARY!g7:g82=c8)+0,(JANUARY!i7:i82=f4)+0,JANUARY!e7:e82))
and
=sumproduct((FEBRUARY!g7:g82=c8)+0,(FEBRUARY!i7:i82=f4)+0,FEBRUARY!e7:e82))

(Where "=c8" and "=f4" refer to an in-cell selection list)
etc

I'd like to be able to cut down my 12 different lines of formul
further to just in line - by refering the sheet name part to a
in-cell selection list (containing the sheet names)

in essence, something like
=sumproduct(((a1)&"!"g7:g82=c8)+0,((a1)&"!"i7:i82=f4)+0,(a1)&"!"e7:e82))
(where "a1" would hold the list of sheet names)

I've had a suggestion that incorporating an INDIRECT reference in plac
of the sheet name part may be the answer,
but I cannot get it to work for me in this instance - it will not refe
to cells on another worksheet

help & ideas are gratefully received!

thank yo

 

[Frank Kabel]

Hi
try
=sumproduct(--(INDIRECT("'" & A1 & "'!g7:g82")=c8),(INDIRECT("'" & A1 &
"'!i7:i82")=f4),INDIRECT("'" & A1 & "'!e7:e82"))

 

[h1]

INDIRECT as Frank showed should work. However, if INDIRECT refers to
another FILE I think that file must be open! Otherwise the result of
your formula will be *REF.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **