If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
make formula look at diff sheet
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. |
#2
|
|||
|
|||
make formula look at diff sheet
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. |
#3
|
|||
|
|||
make formula look at diff sheet
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 |
#4
|
|||
|
|||
make formula look at diff sheet
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? |
#5
|
|||
|
|||
make formula look at diff sheet
=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 . |
#6
|
|||
|
|||
make formula look at diff sheet
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? |
Thread Tools | |
Display Modes | |
|
|