On Sat, 26 Dec 2009 09:09:44 -0800 (PST), wx4usa
wrote:

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,S heet1!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.


In the first ADDRESS function you will need the sheet_text parameter,
like this:

=SUM(INDIRECT(ADDRESS(2,MATCH(A1,Sheet1!B1:M1,0)+1 ,,,"Sheet1")&":"&ADDRESS(2,MATCH(A2,Sheet1!B1:M1,0 )+1)))

Hope this helps / Lars-Åke