Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post

    Copy cell text above if selected cell is blank

    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

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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?

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    That does it! Thanks Rory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •