use an address returned by CELL function in SUMPRODUCT

[Cornelius]

Howdy all. I have a database that gets updated every week
with 4 new columns of info. Every week, a set of columns
of another sheet refers to the last three weeks of data
using a set of SUMPRODUCT functions. I learned here how
to use OFFSET with COUNTA to figure out the cells that
have the most recent (non-blank) entries, and identify
those cells (top and bottom to give me a colun range) on a
sheet. Now two questions:

1) Since these OFFSET cells (call them A1 and A2) return
the cell values I want to use in my SUMPRODUCT, how do I
refer to those values? My SUMPRODUCTs use arguments like
(('sheet1'!AZ1:AZ1500='sheet 2'!B1)*('sheet1'!
BB1:BB1500='sheet 2'!B5)). Instead of manually adjusting
AZ and BB to become 4 columns farther along every week
(over multiple sheets!), how do I use the values of A1 and
A2 in place of AZ1:AZ1500?

2) In the above SUMPRODUCT the columns refered to in the
two arguments progress forward the same way (one over, or
two over, etc.). How can I use the same cells from above
(A1 and A2) but increased to refer to subsequent columns
(i.e. AZ becomes BB)?

Thanks in advance.
Cornelius

 

[Frank Kabel]

Hi
if you store the offset values in A1 and B1 (e.g. 2 / 4)
use something like
=SUMPRODUCT((OFFSET('sheet1'!AZ1:AZ1500,0,A1)='sheet
2'!B1)*(OFFSET('sheet1'!BB1:BB1500,0,B1)='sheet 2'!B5))

this would move the two ranges by A1 columns and B1 columns
respectively

 

[Guest]

Thanks again, Frank!

-----Original Message-----
Hi
if you store the offset values in A1 and B1 (e.g. 2 / 4)
use something like
=SUMPRODUCT((OFFSET('sheet1'!AZ1:AZ1500,0,A1)='sheet
2'!B1)*(OFFSET('sheet1'!BB1:BB1500,0,B1)='sheet 2'!B5))

this would move the two ranges by A1 columns and B1 columns
respectively

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:1308c01c4435e$b2f5b350 [email protected]...

.