Re: Using an address reurned by the the CELL("address") function
From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 05/28/04
- Next message: Gord Dibben: "Re: Automatically displaying a date"
- Previous message: anonymous_at_discussions.microsoft.com: "Re: IF AND DATABASE FUNCTIONS"
- In reply to: Cornelius: "Re: Using an address reurned by the the CELL("address") function"
- Next in thread: Cornelius: "Re: Using an address reurned by the the CELL("address") function"
- Reply: Cornelius: "Re: Using an address reurned by the the CELL("address") function"
- Reply: Harlan Grove: "Re: Using an address reurned by the the CELL("
- Messages sorted by: [ date ] [ thread ]
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 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" <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" 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?
>>>>>>>
>>>>>>> .
>>>>>>>
>>>>>> .
>>>>>>
>>>>
>>>>
>>>> .
>>
>> .
- Next message: Gord Dibben: "Re: Automatically displaying a date"
- Previous message: anonymous_at_discussions.microsoft.com: "Re: IF AND DATABASE FUNCTIONS"
- In reply to: Cornelius: "Re: Using an address reurned by the the CELL("address") function"
- Next in thread: Cornelius: "Re: Using an address reurned by the the CELL("address") function"
- Reply: Cornelius: "Re: Using an address reurned by the the CELL("address") function"
- Reply: Harlan Grove: "Re: Using an address reurned by the the CELL("
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
