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

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 05/28/04 

Date: Fri, 28 May 2004 23:19:27 +0200

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
Cornelius wrote:
> 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
>>
>>
>> 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 ***, 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 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?
>>>>>>>
>>>>>>> .
>>>>>>>
>>>>>> .
>>>>>>
>>>>
>>>>
>>>> .
>>
>> .