Deleting cells depending on content of another cell

[drgogo]

Hiya, I hope some one can help

I have a number of column in my spread sheet, if one cell in a column contains certain data, then i need another cell to remain blank.

An example

Column 1 contains a ref number only if Column 2 contains a letter
But if Column 4 contains either S, B, or E then if anything is in Column 2 this is then cleared from that cell

Hope that explains everthing.

Gareth

[teylyn]

Hi,

you can not delete a manually entered value from a cell with a function in another cell. You'd need VBA to do that.

You can use an IF statement in the cell, so that if a condition is met, then a value is displayed. Something along the lines of

=IF(match({"S","B","E"},D1)>0,"A","")

[shg]

=IF(match({"S","B","E"},D1)>0,"A","")

Teylin, that formula returns "A" for any character in D1 other than T through Z and a few odd others.

Maybe =IF(OR(D1 = {"S","B","E"}), "A", "")?

[teylyn]

Ooops, forgot to test for a true match, so my correction would be

=IF(ISNA(MATCH({"S","B","E"},D1,0)),"","A")

[shg]

=IF(ISNA(MATCH({"S","B","E"},D1,0)),"","A")

That only works for "S" ...

[teylyn]

my head's not screwed on properly today. I'd better stop giving people suggestion.

this one should work, though

=IF(ISNA(MATCH(D1,{"S","B","E"},0)),"","A")

[shg]

That oughta work