Find the last entry in 3 rows!

  • Thread starter Thread starter Paul Watkins
  • Start date Start date
P

Paul Watkins

Hi
Is there any way to use this formula to give the result from 3 columns
instead of 1 (A B & C) ?

=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))

Data (Text & Numbers) in the columns like this
A1 = Ice Cream 12 tubs
B2 = Lollipops packs of 5
C3 = Choc Ices box of 5


Thanks
Paul


Frank Kabel said:
Hi Paul
depends on the kind of values in column A
1. If you have only text values in column A try
=INDEX(Entry!A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(Entry!A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(Entry!A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))
Click to expand...

Hi, Paul.

Jason's line should work just fine (it does for me).
Be sure to check that you have automatic calcuation
turned on (Tools->Options->Calculations) or hit F9.
(Note you'll get a zero if any row (like A34)is blank)

BTW if the values were entered 'across' the sheet,
say always in row 3, the formula should be changed
by substituting 3 for A.

HTH.
jeff
 
Hi Paul
what do you exactly want (a concatenqation of the last row or each last
cell per row)?
 
Hi Frank
I think i found a way round it.
What i've done is added a concatate in Column D which looks at A1, B1 & C1
and displays the 'filled' cell in that row. Then i've placed the formula
below into column E. This then displays a '0' because of the concatate
formula in D1. So to get around that i've added vb code under the sheet to
transfer the 'Text' value of E1 to F1. This then displays the correct value
of 'Ice Cream'.
A bit long winded, but it gives me the result i want.

Thanks again.

Paul
 
Back
Top