Hi!

Try this:

=SUMPRODUCT((INDIRECT(TEXT($A$24,"mmmmyy")&"!D2:D2 47")<=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!Y2:Y2 47")=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!C2:C2 47")=$A2))

Biff

"VBA Noob" wrote in
message ...

Hi all,

Can someone help with this formula,

Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24

Trying to use the indirect function to ref a sheet called July06 and
other ranges here a example of one range =July06!$D$2:$D$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B $1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$2 47="&$A2)))))

Any help would be appreciated


VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=561760