make formula look at diff sheet

[wx4usa]

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,Sheet1!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.

 

[T. Valko]

Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B1: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!B1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,Sheet1!B1:M1,0)))

 

[Lars-Åke Aspelin]

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,Sheet1!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

 

[wx4usa]

Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B1: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!B1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,Sheet1!B1:M1,0)))

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?

 

[excelent]

=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:

 

[T. Valko]

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

Try it like this...

=SUM(INDIRECT(ADDRESS(3,MATCH(Sheet1!$C$9,Sheet1!B1: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!B1:M1,0)):INDEX(Sheet1!B3:M3,MATCH(Sheet1!$C$10,Sheet1!B1:M1,0)))

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?