Re: Find the last entry in 3 rows!

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Paul Watkins (paul.watkins4_at_ntlworld.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 17:34:38 -0000

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 *** 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

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23pC$mTFCEHA.3344@tk2msftngp13.phx.gbl...
> Hi Paul
> what do you exactly want (a concatenqation of the last row or each last
> cell per row)?
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> Paul Watkins wrote:
> > 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" <frank.kabel@freenet.de> wrote in message
> > news:OSJHnEBCEHA.2404@TK2MSFTNGP11.phx.gbl...
> >> 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))))
> >>
> >
> > 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 ***,
> > say always in row 3, the formula should be changed
> > by substituting 3 for A.
> >
> > HTH.
> > jeff
> >> -----Original Message-----
> >> Thanks for the reply.
> >> I tried a demo *** with the following:-
> >>
> >> Cell A1 = Test1
> >> Cell A2 = Test2
> >> Cell A3 = Test3
> >> but all it gives me is the value in cell A1 (Test1) all
> > the time.
> >> What i need is to display the last entry input into
> > column A which in the
> >> example above would be 'Test3' but that might also be
> > A34 or A67 for
> >> example.
> >>
> >> Paul
> >> "Jason Morin" <jason.morin@us.exel.com> wrote in message
> >> news:810701c40769$cd49e8b0$a601280a@phx.gbl...
> >>> One way:
> >>>
> >>> =INDEX(Entry!A:A,COUNTA(Entry!A:A))
> >>>
> >>> HTH
> >>> Jason
> >>> Atlanta, GA
> >>>
> >>>> -----Original Message-----
> >>>> Hi
> >>>> I need some help with the following.
> >>>> I Have a workbook with 2 sheets (Entry & Result). On
> >>> the 'Entry' *** i
> >>>> have various people inputing requests cell by cell.
> > e.g:
> >>>>
> >>>> Person 1 enters request into cell A1
> >>>> Person 2 enters request into cell A2
> >>>> Person 3 enters request into cell A3 and so on.
> >>>>
> >>>> I want then, the 'Result' *** -Cell B6 always to
> >>> display the last entered
> >>>> request on the 'Entry' ***, which could be A3, A4 or
> >>> A100 etc...
> >>>> How do i do this?
> >>>>
> >>>>
> >>>> Thanks in advance
> >>>>
> >>>> Paul
>

Quantcast