Display contents from the next row and previous cell

Hattie

New Member
Joined
Oct 7, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
solved.

i managed to get my commas in the right place.

the following works

=OFFSET(A1,MAX((B:B<>"")*(ROW(B:B))),0)
 
Upvote 0
Solution
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

Excel Formula:
=INDEX(A:A,COUNTA(B:B)+1)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top