use an address returned by CELL function in SUMPRODUCT

  • Thread starter Thread starter Cornelius
  • Start date Start date
C

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
 
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
 
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]...

.
Click to expand...
 
Back
Top