Copy cell text above if selected cell is blank

Topic

New Reply

Hi All,

A1 is Smith. A2, A3 and A4 are blank. A5 is Jones. A6 is blank. A7 is Green. A8, A9, A10, A11 are blank, etc.

I am looking for a formula or bit of code that will do the following:

I would like the code to read that A2 is blank and get name from A1, A3 is blank, get the name from A2, and so on. When a cell is not blank, skip to next cell, if blank, repeat above.

I looked around but couldn’t find another posting with this request. If there is one, just point me in that direction.

Any help appreciated.

Meleia

Reply | Quote

Replies

Can you clarify a bit? A2,A3,A4 are blank so the answer for A2 would be Smith. What would you want for answers for A3 and A4?

Reply | Quote

Sorry for any confusion. A2, A3 and A4 would be Smith. A5 is Jones, so A6 would be Jones. A7 is Green, so A8, A9, A10, A11 would be Green and so on. In other words, the value that is not blank would be copied down until a non-blank cell is encountered, then that value would be copied down until another non-blank cell is encountered.
Hope this clarifies.
Meleia

Reply | Quote

Select the data (including blanks). Press f5, then press the ‘Special…’ button, and choose Blanks from the options and press OK. Now type = then press the up arrow, then press Ctrl+Enter.

Reply | Quote

That does it! Thanks Rory.

Reply | Quote