A twist to my earlier array problem
Posted by Russell Hauf on November 20, 2001 11:36 AM
Referring to the formula:
=SUMPRODUCT((Sheet1!A1:A10=A1)*(Sheet1!B1:B10=B1)*(Sheet1!C1:C10=C1)*(Sheet1!D1:D10))
that was given as an answer to my earlier post (link below),
could I change this formula to dynamically select a column other than D? So suppose I want to use cell G1 on Sheet2 to hold the column number of the number I want to return.
So the part that needs to change is:
(Sheet1!D1:D10)
I know that I could use the ADDRESS function along with FIND, LEFT, and COLUMN to change the D, like LEFT(ADDRESS(1,COLUMN($G$1),2),FIND("$",C50)-1) --
and then use INDIRECT to change function where the D1:D10 is, but is there a better way?
Thanks again,
Russell
Posted by Aladin Akyurek on November 20, 2001 11:53 AM
Russell --
You can name the ranges of interest in Sheet1:
Sheet1!A1:A10 --> aRange (or better: a meaningful name)
Sheet1!B1:B10 --> bRange
....
Sheet1!G1:G10 --> gRange
In G1 enter: gRange (or any other name of the named range) and use instead:
=SUMPRODUCT((aRange=A1)*(bRange=B1)*(cRange=C1),(INDIRECT(G1)))
assuming that gRange is a range that must be summed.
Aladin
Posted by Russell Hauf on November 20, 2001 12:09 PM
That's a good idea, but the number of rows will change in this sheet from month to month. I guess I could name the ranges dynamically from VBA each month (I have other code that will run anyway).
Thanks again for your help, you are truly an Array Formula Master!
-rh --
Posted by Juan Pablo on November 20, 2001 12:15 PM
But, without VBA you can also make them Dynamic using OFFSET, something like:
=OFFSET(G1,0,0,COUNTA(G:G))
This will make grange, or whatever you name it, go from G1 to the last row where G has data.
Juan Pablo
Posted by Russell Hauf on November 20, 2001 12:25 PM
Great idea, thank you. But, without VBA you can also make them Dynamic using OFFSET, something like: =OFFSET(G1,0,0,COUNTA(G:G)) This will make grange, or whatever you name it, go from G1 to the last row where G has data. Juan Pablo : That's a good idea, but the number of rows will change in this sheet from month to month. I guess I could name the ranges dynamically from VBA each month (I have other code that will run anyway).
Posted by Aladin Akyurek on November 20, 2001 12:28 PM
Named dynamic ranges...
=OFFSET(G1,0,0,COUNTA(G:G)) This will make grange, or whatever you name it, go from G1 to the last row where G has data.
I aggree with Juan. However, you have multiple ranges to consider, so you need to reduce to minimize using volatile functions like COUNTA.
Activate Insert|Name|Define.
Enter MaxRecs as name in the Names in the Workbook.
Enter in the Refers To box:
=MATCH(9.99999999999999E+307,Sheet1!$D:$D)
I've chosen a column of numeric type intentionally.
Now activate A1 in Sheet1.
Activate Insert|Name|Define.
Enter aRange (or a better meaningful name) in the Names in the Workbook box.
Enter in the Refers To box:
=OFFSET(Sheet1!$A$1,0,0,MaxRecs,1)
Repeat this proc for the rest of ranges of interest.
Now you can use aRange everywhere in your workbook.
Aladin
========= : That's a good idea, but the number of rows will change in this sheet from month to month. I guess I could name the ranges dynamically from VBA each month (I have other code that will run anyway).
Posted by Russell Hauf on November 20, 2001 12:57 PM
Re: Named dynamic ranges...
Posted by Russell Hauf on November 20, 2001 1:01 PM
Re: Named dynamic ranges...(sorry, last post empty)
(what a rookie I am)
Another good idea, Aladin - however, I can't seem to get the dynamic ranges to work in my SUMPRODUCT formula. I can get them to work when I explicitly define the ranges, though.
Ex:
My range is named rng4, and I have rng4 in cell G1. When I try the sumproduct formula:
=SUMPRODUCT((aRange=A1)*(bRange=B1)*(cRange=C1),(INDIRECT(G1))), I get #REF!.
I think I'll just define the names in VBA...
Posted by Aladin Akyurek on November 20, 2001 3:04 PM
Re: Named dynamic ranges...
Mea culpa. That boils down to a second level of direction, which will end up in #REF! indeed. I think I'll just define the names in VBA...
How about using a condition, e.g.,
=SUMPRODUCT((aRange=A1)*(bRange=B1)*(cRange=C1),(IF(G1="Rng4",Rng4,Rng5)))
Would this be sufficient?
Otherwise, you could try your original idea using indirect and address.
Aladin
Posted by Russell Hauf on November 20, 2001 3:11 PM
Re: Named dynamic ranges...
Thank you. I think I'll just set the ranges at run-time each month. It should take all of about 3 seconds. I appreciate your time.
Russell
