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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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