Hi everyone,
I have 2 spreadsheets, Spreadsheet 1 has a beginning batch number in Col. C,
an ending batch number in Col. E, and a tape number in Col. F. The other
spreadsheet contains batch numbers in Col. H. I need to get the tape number
from Spreadsheet #1 that corresponds with the batch number in Spreadsheet #2;
the batch number can fall anywhere within the range of beginning and ending
batch numbers. I just started to realize the value in using SUMPRODUCT and
got the correct tape number using that function when I tried it on
Spreadsheet #2:
{=SUMPRODUCT((C2:C8=C2)*(E2:E8<=E2)*F2:F8)}

However, when I tried to create the formula on Spreadsheet #1 and have it
refer back to the other spreadsheet I ended up with what I'll probably get
for Christmas from the CIO. Obviously I've missed something major here; can
anyone help me?
{=SUMPRODUCT(('[film1998-2003a.xls]2003'!$C$2:$C$8=H2*('[film1998-2003a.xls]2003'!$E$2:$E$8<=H2)*'[film1998-2003a.xls]2003'!$F$2:$F$8))}