Prevent entry in cell i cell is not blank

[MadBern]

Hey

Is it possible to use Data Validation to prevent someone from overwriting text in cell if there is already text in it?
So that the cell has to be cleared before you can type in something new?

I've tried =NOT(ISBLANK(A1)) and a few other formulas, but none worked.

Regards
Mads

 

[DSCfromCFA]

G'day MB,

I am in no way an expert, but my feeling is that this can only be done with VBA.

Without VBA cells can (aside from being blank) contain either manually entered data or a formula, meaning that there is no means to have a formula in a cell dictate what data is entered into the same cell.

The closest you can come is to use conditional formatting to visually indicate that this cell should not have data entered.

Cheers

shane

 

[MadBern]

G'day MB,

I am in no way an expert, but my feeling is that this can only be done with VBA.

Without VBA cells can (aside from being blank) contain either manually entered data or a formula, meaning that there is no means to have a formula in a cell dictate what data is entered into the same cell.

The closest you can come is to use conditional formatting to visually indicate that this cell should not have data entered.

Cheers

shane

Well, you can dictate that, at least to some extent. In cell AB9 I have the following Data Validation formula: =COUNTIF($S:$S,AB9)
This states that if you try and type something in cell AB9 that isn't found in range S:S, you'll get an error.

However, you might be right, that dictating that the cell must be clear before something new can be typed in is something that only can be achieved though VBA. Which doesn't help me since this workbook is used only in Excel Online, and macros aren't supported there.


Regards,
Mads