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

From: David McRitchie (dmcritchie_at_msn.com)
Date: 05/28/04 

Date: Fri, 28 May 2004 14:59:52 -0400

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 work*** 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" <anonymous@discussions.microsoft.com> wrote in message news:145a601c444e2$efb2d9f0$a401280a@phx.gbl...
>
> Aladin and Frank:
>
> Wish I could just attach the workbook!
>
> The "MASTER" *** 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 *** is always the number "1".
>
> The "Salesperson" *** 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
> spread*** 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 *** 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
> >>***. 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='*** 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?
> >>
> >>.
> >>
> >.
> >