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

[Craigk19]

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?

 

[DSCfromCFA]

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

 

[Craigk19]

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.

 

[NoSparks]

Is this not the same as your other question ?

 

[Craigk19]

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.

 

[DSCfromCFA]

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