is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ?

[jpr.charron]

I am trying to fill in Column F, rows 2, 3, 4, the corresponding date in Row 1 through the OFFSET Function, for the last filled cell (Non-zero) in each row.
However, my Range B2:E4 is filled with SUMPRODUCT formulas, to summarize data from further below,
which seem to prevent me from retrieving the last non-zero cell in each row.

I used a given LOOKUP formula from the forums, it does not give me an error
but no cell address either. I am puzzled.

Help appreciated
Celeste
Col A Col B Col C Col D Col E Col F
7/28 7/29 7/30 7/31 Last Date Expected result
e 0 650 400 0 7/30
k 600 0 100 300 7/31
m 500 100 0 0 7/29
Value 200 150 100 300
Code m e k k
Value 600 500
Code k e
Value 300 100 400
Code m m e

 

[Claus Busch]

Hi Celeste,

Am Sun, 27 Jul 2014 17:25:52 -0700 (PDT) schrieb (e-mail address removed):

Col A Col B Col C Col D Col E Col F
7/28 7/29 7/30 7/31 Last Date Expected result
e 0 650 400 0 7/30
k 600 0 100 300 7/31
m 500 100 0 0 7/29
Value 200 150 100 300
Code m e k k
Value 600 500
Code k e
Value 300 100 400
Code m m e

in F2 try:
=INDEX($1:$1,,LOOKUP(2,1/(A2:E2<>0),COLUMN($1:$1)))
and copy down


Regards
Claus B.

 

[jpr.charron]

Thank you again.
I am getting close but getting the wrong dates.
How does this syntax work ?
INDEX($1:$1),, refers to my Date Row in Row 1
LOOKUP(2,1/ Here I am guessing, Start looking one Row down, one Column right ?
(A2:E2<>0), My first Data Range Row, inclusive of Column A
COLUMN($1:$1))) Column Range in Row 1

My actual case with the adapted formula in AK8
Col A Col F Col G Col H Col I Col AK
Row 4 7/28 7/29 7/30 7/31 Last Date
Row 8 e 0 650 400 0
Row 9 k 600 0 100 300
My Date Range starts in F4, My Data Range starts in F8
My Last Date Formula in AK8
=INDEX($4:$4,,LOOKUP(2,1/(F8:AD8<>0),COLUMN($4:$4)))

Thank you again,
Celeste

 

[Claus Busch]

Hi Celeste,

Am Mon, 28 Jul 2014 03:40:09 -0700 (PDT) schrieb (e-mail address removed):

My Date Range starts in F4, My Data Range starts in F8
My Last Date Formula in AK8
=INDEX($4:$4,,LOOKUP(2,1/(F8:AD8<>0),COLUMN($4:$4)))

then try in AK8:
=INDEX($4:$4,LOOKUP(2,1/(A8:I8<>0),COLUMN($1:$1)))

INDEX($4:$4.. is the range with the data
LOOKUP(2,1/(A8:I8<>0) looks from right to left for the first cell <>0
in range A8:I8
COLUMN(1:1) is a counter

Please look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "SumAndDate"


Regards
Claus B.

 

[jpr.charron]

Ron, Claus, Thanks a lot
Though I still do not understand the first part of this syntax
=LOOKUP(2,1/(my Data Row Range),my Date Row Range)
What are the 2,1/ elements for ?
Meanwhile I will research the LOOKUP syntax.
Thanks again.

 

[Claus Busch]

Hi Celeste,

Am Mon, 28 Jul 2014 10:33:08 -0700 (PDT) schrieb (e-mail address removed):

Though I still do not understand the first part of this syntax
=LOOKUP(2,1/(my Data Row Range),my Date Row Range)
What are the 2,1/ elements for ?

LOOKUP(2,1/(A8:I8<>0)

If you select the part 1/(A8:I8<>9) and press F9 you see if the cell <>
0 1 or if the cell is 0 you see #DIV/0!
So you are looking for 2 and 2 is not available the LOOKUP gives you the
next smaller value and that is the last 1


Regards
Claus B.