Using an address reurned by the the CELL("address") function

C

Cornelius

Hi. Frank Kabel answered my second question from below
(deleted), but I still need help on the first part. I can
get the addresses of the top and bottom of the range I
want to use with OFFSET, but how do I use those addresses
in formulas without copy-and-pasting? Refering to the
cell containing the returned address doesn't work
(SUMPRODUCT tries to use those cells).

***************************************************
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?
 
A

Aladin Akyurek

What formulas do you have in A1 and A2 and what are the current values that
these formulas compute?
 
F

Frank Kabel

Hi
you may stay in the original thread. I think I posted you
a formula suggestion for using OFFSET within SUMPRODUCT.
If this does not help please:
- explain exactly what does not work
- describe your layout in more detail and give some
examples
 
C

Cornelius

Aladin and Frank:

Wish I could just attach the workbook!

The "MASTER" sheet has columns of data. A contains
salesperson names, B contains product codes, C contains
sale of that product. D is empty for our purposes. The
salesperson number on this sheet is always the number "1".

The "Salesperson" sheet has a list of all products in A,
then the last three weeks of sales in D,E and F. I use
SUMPRODUCT((MASTER!B1:B1000=Salesperson!A1)*(MASTER!
A1:A1000=1)*MASTER!C1:C1000) to get the oldest sale of
each product by that salesperson. So the second oldest
info uses MASTER!E,F and G, and the third oldest uses
MASTER!I,J and K. Each week I go through the lists and
find-and-replace the columns for all te salespeople sheets.

Now, using OFFSET I can determine the last entry on
MASTER, and can calculate which columns specifically have
the info for SUMPRODUCT. Using CELL("address") I can
display that column onscreen, and using this any number of
times (18 in this case) I can display the first and last
cells of the range in the above SUMPRODUCT.

If I try and put in the OFFSET function in place of column
names (A,B,C etc.) within the SUPRODUCT formula, the
spreadsheet slows down to the point of unusability, and
can even crash my computer.

Is there a way to calculate those OFFSETs only those 18
times, display them, and then within the SUMPRODUCT refer
to the values of the displayed OFFSETs?

Thus, if the offsetts were in Z1 to Z18, the SUMPRODUCT
might look like this:

SUMPRODUCT((<OFFSET(Z1)>:<OFFSET(Z2)>=Salesperson!A1)*
(<OFFSET(Z3)>:<OFFSET(Z4)>=1)*<OFFSET(Z5)>:<OFFSET(Z6)>)

This would save me 3-4 hours a week (at least) considering
how many of these weekly rollups I do!

Thanks so much again,
Cornelius
 
D

David McRitchie

Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
C

Cornelius

Hi Frank.

Z1 to Z18 contain the CELL("address") references to the
MASTER sheet calculated by OFFSET.
 
C

Cornelius

Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of -4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.

-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Cornelius" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
 
A

Aladin Akyurek

I expected such a formula. OFFSET and CELL are volatile functions. If you
are going to refer to cells housing these CELL/OFFSET formulas in SumProduct
formulas, you'll need INDIRECT, another volatile function. All this will
make your spreadsheet crawl.

Cornelius said:
Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of -4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.

-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Cornelius" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
.
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
I think you may consider a different approach for this (seems to me a
little bit too complicated). If you like email me your file and I'll
have a look at it

--
Regards
Frank Kabel
Frankfurt, Germany

Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of -4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.

-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Cornelius" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
.
Click to expand...
Click to expand...
 
C

Cornelius

I see. INDIRECT returns the value in the referenced cell
in any case; I want to use the cell reference as a cell
reference for a range. Hmmm...
-----Original Message-----
I expected such a formula. OFFSET and CELL are volatile functions. If you
are going to refer to cells housing these CELL/OFFSET formulas in SumProduct
formulas, you'll need INDIRECT, another volatile function. All this will
make your spreadsheet crawl.

Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of - 4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.

-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL ("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function
Click to expand...
being
misused.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Cornelius" <[email protected]> wrote
Click to expand...
in message news:145a601c444e2$efb2d9f0 [email protected]...
Aladin and Frank:

Wish I could just attach the workbook!

The "MASTER" sheet has columns of data. A contains
salesperson names, B contains product codes, C contains
sale of that product. D is empty for our purposes. The
salesperson number on this sheet is always the number "1".

The "Salesperson" sheet has a list of all products in A,
then the last three weeks of sales in D,E and F. I use
SUMPRODUCT((MASTER!B1:B1000=Salesperson!A1)*(MASTER!
A1:A1000=1)*MASTER!C1:C1000) to get the oldest sale of
each product by that salesperson. So the second oldest
info uses MASTER!E,F and G, and the third oldest uses
MASTER!I,J and K. Each week I go through the lists and
find-and-replace the columns for all te salespeople sheets.

Now, using OFFSET I can determine the last entry on
MASTER, and can calculate which columns specifically have
the info for SUMPRODUCT. Using CELL("address") I can
display that column onscreen, and using this any
Click to expand...
number
of
times (18 in this case) I can display the first and last
cells of the range in the above SUMPRODUCT.

If I try and put in the OFFSET function in place of column
names (A,B,C etc.) within the SUPRODUCT formula, the
spreadsheet slows down to the point of unusability, and
can even crash my computer.

Is there a way to calculate those OFFSETs only those 18
times, display them, and then within the SUMPRODUCT refer
to the values of the displayed OFFSETs?

Thus, if the offsetts were in Z1 to Z18, the SUMPRODUCT
might look like this:

SUMPRODUCT((<OFFSET(Z1)>:<OFFSET(Z2)>=Salesperson!A1) *
(<OFFSET(Z3)>:<OFFSET(Z4)>=1)*<OFFSET(Z5)>:<OFFSET (Z6)>)

This would save me 3-4 hours a week (at least) considering
how many of these weekly rollups I do!

Thanks so much again,
Cornelius

-----Original Message-----
Hi
you may stay in the original thread. I think I
Click to expand...
posted
you
a formula suggestion for using OFFSET within SUMPRODUCT.
If this does not help please:
- explain exactly what does not work
- describe your layout in more detail and give some
examples

-----Original Message-----

Hi. Frank Kabel answered my second question from below
(deleted), but I still need help on the first part. I
can
get the addresses of the top and bottom of the range I
want to use with OFFSET, but how do I use those
addresses
in formulas without copy-and-pasting? Refering to the
cell containing the returned address doesn't work
(SUMPRODUCT tries to use those cells).

***************************************************
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
Click to expand...
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
Click to expand...
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
Click to expand...
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?

.

.



.
Click to expand...
Click to expand...


.
Click to expand...
 
C

Cornelius

Perhaps there is Dfunction (database function) I could
use. I've had little luck with these, though.

I will attempt to email you the file, Frank.
-----Original Message-----
Hi
I think you may consider a different approach for this (seems to me a
little bit too complicated). If you like email me your file and I'll
have a look at it

--
Regards
Frank Kabel
Frankfurt, Germany

Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of - 4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.

-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL ("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Cornelius" <[email protected]> wrote
Click to expand...
in message news:145a601c444e2$efb2d9f0 [email protected]...
Aladin and Frank:

Wish I could just attach the workbook!

The "MASTER" sheet has columns of data. A contains
salesperson names, B contains product codes, C contains
sale of that product. D is empty for our purposes. The
salesperson number on this sheet is always the number "1".

The "Salesperson" sheet has a list of all products in A,
then the last three weeks of sales in D,E and F. I use
SUMPRODUCT((MASTER!B1:B1000=Salesperson!A1)*(MASTER!
A1:A1000=1)*MASTER!C1:C1000) to get the oldest sale of
each product by that salesperson. So the second oldest
info uses MASTER!E,F and G, and the third oldest uses
MASTER!I,J and K. Each week I go through the lists and
find-and-replace the columns for all te salespeople sheets.

Now, using OFFSET I can determine the last entry on
MASTER, and can calculate which columns specifically have
the info for SUMPRODUCT. Using CELL("address") I can
display that column onscreen, and using this any number of
times (18 in this case) I can display the first and last
cells of the range in the above SUMPRODUCT.

If I try and put in the OFFSET function in place of column
names (A,B,C etc.) within the SUPRODUCT formula, the
spreadsheet slows down to the point of unusability, and
can even crash my computer.

Is there a way to calculate those OFFSETs only those 18
times, display them, and then within the SUMPRODUCT refer
to the values of the displayed OFFSETs?

Thus, if the offsetts were in Z1 to Z18, the SUMPRODUCT
might look like this:

SUMPRODUCT((<OFFSET(Z1)>:<OFFSET(Z2)>=Salesperson!A1)*
(<OFFSET(Z3)>:<OFFSET(Z4)>=1)*<OFFSET(Z5)>:<OFFSET(Z6) )

This would save me 3-4 hours a week (at least) considering
how many of these weekly rollups I do!

Thanks so much again,
Cornelius

-----Original Message-----
Hi
you may stay in the original thread. I think I posted you
a formula suggestion for using OFFSET within SUMPRODUCT.
If this does not help please:
- explain exactly what does not work
- describe your layout in more detail and give some
examples

-----Original Message-----

Hi. Frank Kabel answered my second question from below
(deleted), but I still need help on the first part. I can
get the addresses of the top and bottom of the range I
want to use with OFFSET, but how do I use those addresses
in formulas without copy-and-pasting? Refering to the
cell containing the returned address doesn't work
(SUMPRODUCT tries to use those cells).

***************************************************
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?

.

.



.
Click to expand...
Click to expand...

.
Click to expand...
 
F

Frank Kabel

Hi
send you one possible solution via private email. Using a combination
of SUMPRODUCT, OFFSET, MATCH. e.g. as an example for the last week of
data:
=SUMPRODUCT((OFFSET(MASTER!$A$6:$A$1500,0,MATCH(9.999999E+307,MASTER!$2
:$2,1)-1)=$A$3)*(OFFSET(MASTER!$A$6:$A$1500,0,MATCH(9.999999E+307,MASTE
R!$2:$2,1))=$A6)*OFFSET(MASTER!$A$6:$A$1500,0,MATCH(9.999999E+307,MASTE
R!$2:$2,1)+1))


--
Regards
Frank Kabel
Frankfurt, Germany

Perhaps there is Dfunction (database function) I could
use. I've had little luck with these, though.

I will attempt to email you the file, Frank.
-----Original Message-----
Hi
I think you may consider a different approach for this (seems to me a
little bit too complicated). If you like email me your file and I'll
have a look at it

--
Regards
Frank Kabel
Frankfurt, Germany

Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of - 4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.


-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL ("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

in message [email protected]...

Aladin and Frank:

Wish I could just attach the workbook!

The "MASTER" sheet has columns of data. A contains
salesperson names, B contains product codes, C contains
sale of that product. D is empty for our purposes. The
salesperson number on this sheet is always the
number "1".

The "Salesperson" sheet has a list of all products in A,
then the last three weeks of sales in D,E and F. I use
SUMPRODUCT((MASTER!B1:B1000=Salesperson!A1)*(MASTER!
A1:A1000=1)*MASTER!C1:C1000) to get the oldest sale of
each product by that salesperson. So the second oldest
info uses MASTER!E,F and G, and the third oldest uses
MASTER!I,J and K. Each week I go through the lists and
find-and-replace the columns for all te salespeople sheets.

Now, using OFFSET I can determine the last entry on
MASTER, and can calculate which columns specifically have
the info for SUMPRODUCT. Using CELL("address") I can
display that column onscreen, and using this any number of
times (18 in this case) I can display the first and last
cells of the range in the above SUMPRODUCT.

If I try and put in the OFFSET function in place of column
names (A,B,C etc.) within the SUPRODUCT formula, the
spreadsheet slows down to the point of unusability, and
can even crash my computer.

Is there a way to calculate those OFFSETs only those 18
times, display them, and then within the SUMPRODUCT refer
to the values of the displayed OFFSETs?

Thus, if the offsetts were in Z1 to Z18, the SUMPRODUCT
might look like this:

SUMPRODUCT((<OFFSET(Z1)>:<OFFSET(Z2)>=Salesperson!A1)*
(<OFFSET(Z3)>:<OFFSET(Z4)>=1)*<OFFSET(Z5)>:<OFFSET(Z6) )

This would save me 3-4 hours a week (at least) considering
how many of these weekly rollups I do!

Thanks so much again,
Cornelius

-----Original Message-----
Hi
you may stay in the original thread. I think I posted you
a formula suggestion for using OFFSET within SUMPRODUCT.
If this does not help please:
- explain exactly what does not work
- describe your layout in more detail and give some
examples

-----Original Message-----

Hi. Frank Kabel answered my second question from below
(deleted), but I still need help on the first part. I can
get the addresses of the top and bottom of the range I
want to use with OFFSET, but how do I use those addresses
in formulas without copy-and-pasting? Refering to the
cell containing the returned address doesn't work
(SUMPRODUCT tries to use those cells).

***************************************************
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?

.

.



.
Click to expand...

.
Click to expand...
Click to expand...
 
C

Cornelius

Still scratching my head! Thanks!
-----Original Message-----
Hi
send you one possible solution via private email. Using a combination
of SUMPRODUCT, OFFSET, MATCH. e.g. as an example for the last week of
data:
=SUMPRODUCT((OFFSET(MASTER!$A$6:$A$1500,0,MATCH (9.999999E+307,MASTER!$2
:$2,1)-1)=$A$3)*(OFFSET(MASTER!$A$6:$A$1500,0,MATCH (9.999999E+307,MASTE
R!$2:$2,1))=$A6)*OFFSET(MASTER!$A$6:$A$1500,0,MATCH
Click to expand...
(9.999999E+307,MASTE
R!$2:$2,1)+1))


--
Regards
Frank Kabel
Frankfurt, Germany

Perhaps there is Dfunction (database function) I could
use. I've had little luck with these, though.

I will attempt to email you the file, Frank.
Click to expand...
(seems to me
a
little bit too complicated). If you like email me your file and I'll
have a look at it

--
Regards
Frank Kabel
Frankfurt, Germany


Cornelius wrote:
Hi David. I see what you are saying, but in the Z1 to Z18
CELL functions, I get an absolute result. The formula is
this (in Z1):

=CELL("address",OFFSET(MASTER!$B$6,0,COUNTA(MASTER!
$B$6:$II$6)-4,1,1))

where B6 is the start of the columns of data, II is the
approxomate end of the MASTER sheet, and the offset of - 4
is the cell I want to reference. In this case the value
is displayed as:

[File.xls]MASTER!$BZ$6

There is a corresponding cell beneath it (call it Z2) that
produces:

[ForecasterX2.xls]MASTER!$BZ$1506

Having obtained these results, how do I direct SUMPRODUCT
to use those references in a (Z1:Z2=A3)*etc. calculation?

Thanks.


-----Original Message-----
Other than an unrelated reference in your subject this
is the first time you actually mentioned CELL ("address")

You must have a reference with that worksheet function
to get predictable results.
B8: =CELL("address", A4)
B9: =CELL("address")
press the checkmark to enter cell without moving
and look at the result.
Now select a cell such as F10
Then press the F9 function key to recalculate
and look at the result.

See http://www.mvps.org/dmcritchie/excel/pathname.htm
for more use of crippled formulas involving =Cell ("pathname")
instead of =Cell("pathname",A1) same Function being misused.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

in message [email protected]...

Aladin and Frank:

Wish I could just attach the workbook!

The "MASTER" sheet has columns of data. A contains
salesperson names, B contains product codes, C contains
sale of that product. D is empty for our purposes. The
salesperson number on this sheet is always the
number "1".

The "Salesperson" sheet has a list of all products in A,
then the last three weeks of sales in D,E and F. I use
SUMPRODUCT((MASTER!B1:B1000=Salesperson!A1)*(MASTER!
A1:A1000=1)*MASTER!C1:C1000) to get the oldest sale of
each product by that salesperson. So the second oldest
info uses MASTER!E,F and G, and the third oldest uses
MASTER!I,J and K. Each week I go through the lists and
find-and-replace the columns for all te salespeople sheets.

Now, using OFFSET I can determine the last entry on
MASTER, and can calculate which columns specifically have
the info for SUMPRODUCT. Using CELL("address") I can
display that column onscreen, and using this any number of
times (18 in this case) I can display the first and last
cells of the range in the above SUMPRODUCT.

If I try and put in the OFFSET function in place of column
names (A,B,C etc.) within the SUPRODUCT formula, the
spreadsheet slows down to the point of unusability, and
can even crash my computer.

Is there a way to calculate those OFFSETs only those 18
times, display them, and then within the SUMPRODUCT refer
to the values of the displayed OFFSETs?

Thus, if the offsetts were in Z1 to Z18, the SUMPRODUCT
might look like this:

SUMPRODUCT((<OFFSET(Z1)>:<OFFSET(Z2)>=Salesperson! A1)*
(<OFFSET(Z3)>:<OFFSET(Z4)>=1)*<OFFSET(Z5)>:<OFFSET (Z6) )

This would save me 3-4 hours a week (at least) considering
how many of these weekly rollups I do!

Thanks so much again,
Cornelius

-----Original Message-----
Hi
you may stay in the original thread. I think I posted you
a formula suggestion for using OFFSET within SUMPRODUCT.
If this does not help please:
- explain exactly what does not work
- describe your layout in more detail and give some
examples

-----Original Message-----

Hi. Frank Kabel answered my second question from below
(deleted), but I still need help on the first part. I can
get the addresses of the top and bottom of the range I
want to use with OFFSET, but how do I use those addresses
in formulas without copy-and-pasting? Refering to the
cell containing the returned address doesn't work
(SUMPRODUCT tries to use those cells).

***************************************************
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?

.

.



.

.
Click to expand...
Click to expand...

.
Click to expand...
 
H

Harlan Grove

send you one possible solution via private email. Using a combination
of SUMPRODUCT, OFFSET, MATCH. e.g. as an example for the last week of
data:
=SUMPRODUCT((OFFSET(MASTER!$A$6:$A$1500,0,
MATCH(9.999999E+307,MASTER!$2:$2,1)-1)=$A$3)*(OFFSET(MASTER!$A$6:$A$1500,0,
MATCH(9.999999E+307,MASTER!$2:$2,1))=$A6)*OFFSET(MASTER!$A$6:$A$1500,0,
MATCH(9.999999E+307,MASTER!$2:$2,1)+1))
Click to expand...
...

Better in terms of efficiency to put

=MATCH(9.999999E+307,MASTER!$2:$2,1)

into a separate cell where it'd need to be calculated once no matter how many
times it's accessed via cell reference. If it were put into X99 in the same
worksheet as that containing the formula above, the formula above could be
reduced to

=SUMPRODUCT((OFFSET(MASTER!$A$6:$A$1500,0,$X$99-1)=$A$3)
*(OFFSET(MASTER!$A$6:$A$1500,0,$X$99)=$A6)
*OFFSET(MASTER!$A$6:$A$1500,0,$X$99+1))

Note, though, that since the MATCH call in X99 would return the column number,
BASE ONE, of the rightmost nonblank column in row 2, $X$99-1 as 3rd argument to
OFFSET would give the column in MASTER containing the rightmost nonblank cell in
MASTER!$2:$2 while $X$99 and $X$99+1 as 3rd arguments to OFFSET would give the
columns to the right of that, so presumably all blank, so it shouldn't be a
surprise if the formula above always returned zero.

Did you mean

=SUMPRODUCT((OFFSET(MASTER!$A$6:$A$1500,0,$X$99-3)=$A$3)
*(OFFSET(MASTER!$A$6:$A$1500,0,$X$99-2)=$A6)
*OFFSET(MASTER!$A$6:$A$1500,0,$X$99-1))

?
 
F

Frank Kabel

Harlan Grove wrote:
[....]
into a separate cell where it'd need to be calculated once no matter
how many times it's accessed via cell reference. If it were put into
X99 in the same worksheet as that containing the formula above, the
formula above could be reduced to
=SUMPRODUCT((OFFSET(MASTER!$A$6:$A$1500,0,$X$99-1)=$A$3)
*(OFFSET(MASTER!$A$6:$A$1500,0,$X$99)=$A6)
*OFFSET(MASTER!$A$6:$A$1500,0,$X$99+1))
Click to expand...

totally agree with you on that :)

Note, though, that since the MATCH call in X99 would return the
column number, BASE ONE, of the rightmost nonblank column in row 2,
$X$99-1 as 3rd argument to OFFSET would give the column in MASTER
containing the rightmost nonblank cell in MASTER!$2:$2 while $X$99
and $X$99+1 as 3rd arguments to OFFSET would give the columns to the
right of that, so presumably all blank, so it shouldn't be a surprise
if the formula above always returned zero.
Click to expand...


as I had the advantage to actually use the OP's example file in this
case the offsets are correct. The date value was above the FIRST
relevant data column for a week and not above the LAST relevant one.
But without actually seeing this example file I would have guessed the
same offsets as you did

Regards
Frank
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

use an address returned by CELL function in SUMPRODUCT 2
#value error for sumproduct using RIGHT cmd 0
Dependent drop down dynamic range formula, off sheet. 7
Using an offset formula for the reference in a relative reference 6
CountIf previous cell formula 0
Indirect function and relative references 4
matching full name to 'two column' name using sumproduct 9
Using SUM + INDEX in array formula 5

Top