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.

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.

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

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?

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


"Lars-Ã…ke Aspelin" skrev:

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
.

Why would one use the Indirect function?

Sometimes you can use INDEX to "build" a range reference. This is one of
those times.

Sometimes you can only use INDIRECT to reference a range. One of the best
examples is when you have a named range or a sheet name in a cell:

A1 = MyRange

Assuming MyRange refers to Sales!A1:A10 and you want to sum that range:

=SUM(INDIRECT(A1))

Which would be the equivalent of:

=SUM(Sales!$A$1:$A$10)

Or, when you want to reference a cell/range on a variable sheet:

A1 = Region 1 (a sheet name)

=INDIRECT("'"&A1&"'!X100")

Which would be the equivalent of:

='Region 1'!X100

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
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?