Calculating from last inputted cell

I am trying to write a formula to input the data from the last inputted cell
in a range to another cell. Reading through some earlier threads I found a
formula that works in 2 stages, the first finds the last inputted cell to
return the cell reference, and the 2nd stage reuses the result, the formulas
were

=MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))

If the above formula was entered into cell B1 the 2nd formula would be
entered in the cell where I wanted the data displayed and would be

=INDEX($A:$A,B1)

The 2 formulas work providing the entered data starts at row 1, my problem
is that the entered data that I want to check is partway down the column, I
have tried amending the formula just to cover the range that I want to check
as below

=MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))

Which returns the correct row value for the last inputted cell but I can't
get the 2nd formula to use the reference to display the last inputted value.
The 2nd formula that I have used is

=INDEX($A$20$29:$A$29,B1)

This produces a #REF error, I have also tried to use named ranges which
produces the correct cell reference number but still produces the same error.

I would be greatly obliged for any ideas on how I could get it to work.

Thanks

Petermac

Either adjust the first formula to

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Petermac" <[email protected]> wrote in message
news:[email protected]...
> I am trying to write a formula to input the data from the last inputted
cell
> in a range to another cell. Reading through some earlier threads I found a
> formula that works in 2 stages, the first finds the last inputted cell to
> return the cell reference, and the 2nd stage reuses the result, the
formulas
> were
>
> =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))
>
> If the above formula was entered into cell B1 the 2nd formula would be
> entered in the cell where I wanted the data displayed and would be
>
> =INDEX($A:$A,B1)
>
> The 2 formulas work providing the entered data starts at row 1, my
problem
> is that the entered data that I want to check is partway down the column,
I
> have tried amending the formula just to cover the range that I want to
check
> as below
>
> =MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))
>
> Which returns the correct row value for the last inputted cell but I can't
> get the 2nd formula to use the reference to display the last inputted
value.
> The 2nd formula that I have used is
>
> =INDEX($A$20$29:$A$29,B1)
>
> This produces a #REF error, I have also tried to use named ranges which
> produces the correct cell reference number but still produces the same
error.
>
> I would be greatly obliged for any ideas on how I could get it to work.
>
> Thanks
>
> Petermac

Either adjust the first formula to

=MAX(ROW($A$20:$A$29)*($A$20:$A$29<>""))-MIN(ROW($A$20:$A$29))+1


or just leave the second formula as

=INDEX($A:$A,B1)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Petermac" <[email protected]> wrote in message
news:[email protected]...
> I am trying to write a formula to input the data from the last inputted
cell
> in a range to another cell. Reading through some earlier threads I found a
> formula that works in 2 stages, the first finds the last inputted cell to
> return the cell reference, and the 2nd stage reuses the result, the
formulas
> were
>
> =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))
>
> If the above formula was entered into cell B1 the 2nd formula would be
> entered in the cell where I wanted the data displayed and would be
>
> =INDEX($A:$A,B1)
>
> The 2 formulas work providing the entered data starts at row 1, my
problem
> is that the entered data that I want to check is partway down the column,
I
> have tried amending the formula just to cover the range that I want to
check
> as below
>
> =MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))
>
> Which returns the correct row value for the last inputted cell but I can't
> get the 2nd formula to use the reference to display the last inputted
value.
> The 2nd formula that I have used is
>
> =INDEX($A$20$29:$A$29,B1)
>
> This produces a #REF error, I have also tried to use named ranges which
> produces the correct cell reference number but still produces the same
error.
>
> I would be greatly obliged for any ideas on how I could get it to work.
>
> Thanks
>
> Petermac

Bob

Thankyou very much for your help, I was trying to make it more complecated
that it was.

peter

"Bob Phillips" wrote:

> Either adjust the first formula to
>
> =MAX(ROW($A$20:$A$29)*($A$20:$A$29<>""))-MIN(ROW($A$20:$A$29))+1
>
>
> or just leave the second formula as
>
> =INDEX($A:$A,B1)
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Petermac" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to write a formula to input the data from the last inputted
> cell
> > in a range to another cell. Reading through some earlier threads I found a
> > formula that works in 2 stages, the first finds the last inputted cell to
> > return the cell reference, and the 2nd stage reuses the result, the
> formulas
> > were
> >
> > =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>""))
> >
> > If the above formula was entered into cell B1 the 2nd formula would be
> > entered in the cell where I wanted the data displayed and would be
> >
> > =INDEX($A:$A,B1)
> >
> > The 2 formulas work providing the entered data starts at row 1, my
> problem
> > is that the entered data that I want to check is partway down the column,
> I
> > have tried amending the formula just to cover the range that I want to
> check
> > as below
> >
> > =MAX(ROW($A$20:$A$29)*($A$20:$A$229<>""))
> >
> > Which returns the correct row value for the last inputted cell but I can't
> > get the 2nd formula to use the reference to display the last inputted
> value.
> > The 2nd formula that I have used is
> >
> > =INDEX($A$20$29:$A$29,B1)
> >
> > This produces a #REF error, I have also tried to use named ranges which
> > produces the correct cell reference number but still produces the same
> error.
> >
> > I would be greatly obliged for any ideas on how I could get it to work.
> >
> > Thanks
> >
> > Petermac
>
>
>