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

Craigk19

New Member
Joined
Aug 13, 2019
Messages
8
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top