Highlighting overwritten cells - conditional formatting

[gwoolley0302]

Hi all,

We share an excel template with our network for them to input quarterly monitoring data with us. Some fields we attempt to restrict through drop down lists. These often get overwritten.

So my question is whether we can use a tool to show the user that they've overwritten a dropdown list cell? (without a macro)

I thought maybe conditional formatting could highlight the cell in red if they overwrite the dropdown list?

We also get a similar issue when data is not entered in the format we would ideally like. Again I wonder if we can use a tool to prompt the user that the cell is incorrect (i.e. a red cell indicates it's been inputted incorrectly)

Thanks, Greg

 

[SanjayGMusafir]

So my question is whether we can use a tool to show the user that they've overwritten a dropdown list cell?

There is an 'Error message' option in Data Validation if wrong entry is made.

 

[gwoolley0302]

Hi Sanjay, Thank you. That works well when a user enters text into a cell. However, when a user copies and paste values, the error alert does not work. Any ideas how to solve the issue when a user is pasting content over a dropdown list?

Thanks, Greg

 

[SanjayGMusafir]

Che this and revert - Here DVList is the List used for making Drop Down List

Book1
	A	B	C	D	E
1				List
2		Test 1		Test 1
3		Test 3		Test 2
4		Test 8		Test 3
5		Test 5		Test 4
6		Test 5		Test 5
7		Test New		Test 6
8				Test 7
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=AND(NOT(COUNTIFS(DVList,B1)),B1<>"")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1:B9	List	=DVList

 

[gwoolley0302]

Thanks Sanjay, I have tried this in the below sheet, but it doesn't seem to be working.

1) I'm a little confused on the use of "B1"

2) Occasionally it has worked, but when you then change the overwritten cell to a dropdown value, it (incorrectly) stays in the style of the conditional formatting . Can it revert back to no conditional formatting if corrected?

Thanks for your help.

 

[SanjayGMusafir]

I'm a little confused on the use of "B1"

share the formula you are actually using for Conditional Formatting

Also, Have you created a Namaskared range for I2:i11?

Further, if you copy paste in a cell - Conditional Formatting Rules for that cell may also change as per the source cell.

 

[gwoolley0302]

Thank you Sanjay.

The formula used is =AND(NOT(COUNTIFS($I$2:$I$11,B1)),B1<>"") (Not currently a named range no - is that needed?)

Yeah I did notice that when copy and paste is done, is alters the range that the conditional formatting applies to - e.g. B1:B150000 becomes B1:B7,B9:B150000.

But copy and paste is the issue we're trying to overcome. We're trying to highlight to users they shouldn't do that.

 

[SanjayGMusafir]

But copy and paste is the issue we're trying to overcome

In that case we need a helper column to identify any unexpected data entry in the column.

I just revert with that solution

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F
1			Check		List
2		Test 1	1		Test 1
3		overwrite	0		Test 2
4		Test 8	0		Test 3
5		Test 5	1		Test 4
6		Test 5	1		Test 5
7		Test New	0		Test 6
8					Test 7
9	overwrite
10
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IFNA(LOOKUP(1,1/(B2=E2:E8)),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=AND(C1=0,C1<>"")	text	NO