On Dec 26, 12:37*pm, "T. Valko" wrote:
Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)+1,,,"Sheet1")&":"&ADDRESS(3,MATCH(Sheet1!$ C$10,Sheet1!B1:M1,0)+1)))

This will do the same thing and doesn't use the volatile function INDIRECT:

=SUM(INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$9,Sheet1!B 1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,She et1!B1:M1,0)))

--
Biff
Microsoft Excel MVP

"wx4usa" wrote in message

...

I have this formula that works great if the data is on the same
sheet......=SUM(INDIRECT(ADDRESS(2,MATCH(A1,B1:M1, 0)+1)&":"&ADDRESS
(2,MATCH(A2,B1:M1,0)+1)))

I changed it to this on sheet2......=SUM(INDIRECT(ADDRESS(3,MATCH($C
$9,Sheet1!B1:M1,0)+1)&":"&ADDRESS(3,MATCH($C$10,Sh eet1!B1:M1,0)+1)))

And it returns the 2nd row on the sheet the formula is on rather than
Sheet1.

Any help would be appreciated. Not exactly sure how this bulletin
board works. *Thanks.

Thanks Biff, I like the IndexMatch better it works great and will
allow me to drag down easier for more data. Why would one use the
Indirect function?