This works -- it consistently gives me the right answers. So I'm reluctant to tamper with it. BUT if there's a better way, I'd love to hear it.
This is the explanation I sent to my manager on how it works; probly unnecessary for most of you, but here goes:
--------
=SUM(OFFSET(INDIRECT(ADDRESS(MATCH($Y19,Dollars!$F:$F,0),10,,,"Dollars")),0,$B$8,1,$B$11-$B$8+1))
From the inside out:
MATCH finds the row number for the given UPC number (Y19) in the Dollars sheet, column F.
ADDRESS constructs text address out of that row number, column 10 (last text column before date/dollars data), and the "Dollars" sheet (there's another version of this formula that looks at the "Units" sheet).
INDIRECT interprets that text address as a usable range.
OFFSET creates a dynamic range based on user choices:
first argument (reference): begins at the address returned by the rest of the formula.
second argument (rows): offset 0 rows (stay in same UPC row)
third argument (columns): offset number of columns equal to Begin Week (B8), which is chosen from drop-down menu.
fourth argument (height of range): 1 row
fifth argument (width of range): full date range = End Week - Begin Week (B11-B8+1); the +1 makes it inclusive of both begin week and end week.
SO, the OFFSET constructs a range based on UPC row number, and user date choices.
Then SUM does what it does best, and voila, total dollars for given date range.
OK?
--------
What do you think?
Catherine
This message was edited by artslave on 2002-05-07 21:37
This is the explanation I sent to my manager on how it works; probly unnecessary for most of you, but here goes:
--------
=SUM(OFFSET(INDIRECT(ADDRESS(MATCH($Y19,Dollars!$F:$F,0),10,,,"Dollars")),0,$B$8,1,$B$11-$B$8+1))
From the inside out:
MATCH finds the row number for the given UPC number (Y19) in the Dollars sheet, column F.
ADDRESS constructs text address out of that row number, column 10 (last text column before date/dollars data), and the "Dollars" sheet (there's another version of this formula that looks at the "Units" sheet).
INDIRECT interprets that text address as a usable range.
OFFSET creates a dynamic range based on user choices:
first argument (reference): begins at the address returned by the rest of the formula.
second argument (rows): offset 0 rows (stay in same UPC row)
third argument (columns): offset number of columns equal to Begin Week (B8), which is chosen from drop-down menu.
fourth argument (height of range): 1 row
fifth argument (width of range): full date range = End Week - Begin Week (B11-B8+1); the +1 makes it inclusive of both begin week and end week.
SO, the OFFSET constructs a range based on UPC row number, and user date choices.
Then SUM does what it does best, and voila, total dollars for given date range.
OK?
--------
What do you think?
Catherine
This message was edited by artslave on 2002-05-07 21:37