Display contents from the next row and previous cell

[Hattie]

Hello,

How do you display the data in a cell of Column A based on the last data entry in column B

So column A has a list of dates in it say 20 rows (A1:A20)

Column B is blank

But as time goes on the cells in column B get filled in with people’s names.

So say the first three rows (B1, B2, B3) of column B have names in them, Col A has 20 dates (A1:A20)

I want to display the date from next free row (cell A4) as cell B4 is the next empty cell.

Basically I though, I would search col B for the last entry (B3 in this case) and use the offset function

to display the cell down one and left one (so down to row 4 and left to col A), voila. I thought!!!!

I tried using the formula

LOOKUP(2,1/(B:B<>""),B:B)

To find the last cell and it works but it gives me the contents of the last cell used not its reference.

OK that make sense.

I then tried wrapping it in an index(), then a cell(), then a row() function to get a cell reference

and then use the offset() function with a "1,-1" to get the previous column and next row down)

but I failed miserably, I’m always getting an error.

I obviously haven't got my head around it

Could anybody give me a pointer please.

Thank you

 

[Hattie]

solved.

i managed to get my commas in the right place.

the following works

=OFFSET(A1,MAX((B:B<>"")*(ROW(B:B))),0)

 

[Peter_SSs]

A couple of points ..

1. That uses the volatile function OFFSET. I would recommend avoiding volatile functions if viable alternatives are available, which certainly is the case here.
   
   
2. If any rows are subsequently added at the top of the sheet (eg to insert headings), that formula will return an incorrect result.

.. and a suggestion.
Try this non-volatile option instead

=INDEX(A:A,COUNTA(B:B)+1)