Results 1 to 6 of 6

Thread: If cell contains specific text then clear contents of a different cell
Thanks Thanks: 0 Likes Likes: 0

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

    Default If cell contains specific text then clear contents of a different cell

    If Cell B1 contains specific text, example "In", how to have it automatically ClearContents of Cell A1? would like to make this work for a range of cells if G3:G12 contain the word "In" then have them clear the corresponding C3:C7. Is this possible through Conditional formatting or is a Macro needed?

  2. #2
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If cell contains specific text then clear contents of a different cell

    G'day Craig,

    In cell A1 =IF(B1="In","",Formula that puts data in A1)

    However if data is manually put in A1 then that will not work, although you could move your manual entry cell to another cell (say AB1) and have:

    =IF(B1="In","",AB1)

    The second part of your question is not clear. Do you want to clear ALL of C3:C7 if ALL of G3:G12 contain "In", or is there some correspondence between what cell(s) contain "In" and what cell(s) are cleared.

    Cheers

    shane
    Be warned - I'm an Excel enthusiast, not an expert.

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

    Default Re: If cell contains specific text then clear contents of a different cell

    Quote Originally Posted by DSCfromCFA View Post
    G'day Craig,

    In cell A1 =IF(B1="In","",Formula that puts data in A1)

    However if data is manually put in A1 then that will not work, although you could move your manual entry cell to another cell (say AB1) and have:

    =IF(B1="In","",AB1)

    The second part of your question is not clear. Do you want to clear ALL of C3:C12 if ALL of G3:G12 contain "In", or is there some correspondence between what cell(s) contain "In" and what cell(s) are cleared.

    Cheers

    shane
    It would be when data is manually put in A1

    the 2nd part would be the correspondence between cell(s) containing "In" and what cells to clear. So if G5 and G7 where changed to the "In" status then C5 and C7 would auto clear.

  4. #4
    Board Regular
    Join Date
    Mar 2013
    Posts
    805
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell contains specific text then clear contents of a different cell

    Is this not the same as your other question ?

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

    Default Re: If cell contains specific text then clear contents of a different cell

    Quote Originally Posted by NoSparks View Post
    Is this not the same as your other question ?
    It is, I looked for a day and half and could not find any information or direction for this problem. The other question was specifically for my use case. I have this thread going so anyone that needs something of this nature in the future will be able to find it with a search either with google or within this forum.

  6. #6
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If cell contains specific text then clear contents of a different cell

    G'day Craig,

    Thanks for the clarifications. The manual entry into the cells you want to clear presents a problem as you cannot have both the manual entry and a function in the same cell.

    The most common solution is as I descried above - use another cell for the manual entry and have a function in your primary cell either reflects the manual entry or writes in an alternative with an IF function - note that IF functions can be nested to allow multiple alternatives.

    A possible solution that would work if you just want to stop displaying the data in the manual entry cell, rather than replace it, is to use conditional formatting to change the font colour to match the cell fill colour when "In" is in the corresponding cell. This won't be of any use if their is any manipulation or referenceing of the cell that expects the cell to be empty.

    Hope that helps

    cheers

    shane
    Be warned - I'm an Excel enthusiast, not an expert.

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
  •