Results 1 to 8 of 8

Thread: Copy value from cell above if blank

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy value from cell above if blank

    In column A every two cells are merged (A1 with A2, A3 with A4 etc...) so my formula only works properly for odd number rows. I need the below to use the value from the cell above if A6 is empty and so on.

    =IF(Sheet1!$B6<>"", Sheet1!$A6,"")

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,454
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copy value from cell above if blank

    Maybe:
    Code:
    =IF(Sheet1!$B6<>"", IF(MOD(ROW(Sheet1!A6),2)=0,Sheet1!$A5,""))
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy value from cell above if blank

    Quote Originally Posted by JoeMo View Post
    Maybe:
    Code:
    =IF(Sheet1!$B6<>"", IF(MOD(ROW(Sheet1!A6),2)=0,Sheet1!$A5,""))
    Did not work. It fixed the even number rows but now the odd ones are blank.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Copy value from cell above if blank

    Maybe
    =IF(Sheet1!$A6="",Sheet1!$A5,Sheet1!A6)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy value from cell above if blank

    Quote Originally Posted by Fluff View Post
    Maybe
    =IF(Sheet1!$A6="",Sheet1!$A5,Sheet1!A6)
    Also did not work for what I'm trying to do but maybe it's my fault as maybe I wasn't clear enough with what I need. Ignore my first post and see the examples below. I need a formula to output the same results as in Sheet2.

    In Sheet2 Column B I'm using the formula and it works perfectly. =IF(Sheet1!$B1<>"", Sheet1!$B1,"")

    I need some help with the column A formula as the following only works with odd number rows. =IF(Sheet1!$B1<>"", Sheet1!$A1,"")


    Sheet1 A B
    1 Name1 500
    2
    3 Name2
    4
    5 Name3 600
    6 300
    7 Name4
    8 400


    Sheet2 A B
    1 Name1 500
    2
    3
    4
    5 Name3 600
    6 Name3 300
    7
    8 Name4 400

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,974
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Copy value from cell above if blank

    In cell A1 of Sheet2, try this formula and copy down.

    =IF(Sheet1!B1="","",LOOKUP("zzz",Sheet1!A$1:A1))
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy value from cell above if blank

    Quote Originally Posted by Peter_SSs View Post
    In cell A1 of Sheet2, try this formula and copy down.

    =IF(Sheet1!B1="","",LOOKUP("zzz",Sheet1!A$1:A1))
    Thanks. This one worked but I already got it working with the formula below.

    =IF(Sheet1!$B2<>"",IF(Sheet1!$A2<>"",Sheet1!$A2,Sheet1!$A1),"")

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,974
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Copy value from cell above if blank

    Quote Originally Posted by Mux99 View Post
    Thanks. This one worked but I already got it working with the formula below.

    =IF(Sheet1!$B2<>"",IF(Sheet1!$A2<>"",Sheet1!$A2,Sheet1!$A1),"")
    That sort of indicates that in fact your data & results probably actually start on row 2 of each sheet, not row 1 as indicated in your tables in post 5? (Or else that formula is entered on row 2 of Sheet2 and copied up to row 1 which, while working actually creates a #REF ! error in the formula.

    If everything (apart from any headings) actually starts on row 2, then you could also use this simpler version in A2 of Sheet2, copied down

    =IF(Sheet1!B2="","",Sheet1!A1&Sheet1!A2)
    Last edited by Peter_SSs; Sep 17th, 2019 at 05:08 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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