Making sure cells in a column contain same entry

tigerdel

New member
Joined
Aug 23, 2012
Messages
40
Reaction score
0
Points
0
Location
Cambridgeshire
Excel Version(s)
Office 365
Hi All

I am collecting data from various sites and need to ensure that the data entered into the cells in column L [Facility] are all identical and if not, highlighted those that are different from L5, warn the user and then open the sheet for them to correct

Is this possible

sample sheet attached which would be populated via a userform View attachment Sample DCW.xlsb

Thanks again

Derek
 

joseph4tw

New member
Joined
May 28, 2012
Messages
13
Reaction score
0
Points
0
Location
South Florida, USA
Website
www.spreadsheetsmadeeasy.com
You could use regular cell validation for this if you like.

Highlight cells L5 to L87. Click Data-->Data Validation-->Custom and then enter this formula:
Code:
=IF(AND(L5<>"",L5=$L$5),TRUE,FALSE)

As long as they start with L5, everything else has to match L5 as they go to L6, L7 and so on.

The only caveat is if they change L5 again, the rest won't complain until you try to re-enter them again.

To get those values to show up as red, you could put a conditional format on top of this.

Highlight L5 to L87 again, click Home-->Conditional Formatting --> New Rule...

Click "Use a formula to determine..." and in the formula bar, do a slightly different formula:
Code:
=IF(AND(L5<>"",L5<>$L$5),TRUE,FALSE)

Click Format --> Fill --> Red. OK --> OK.


If that doesn't suit your needs, please let me know and we can go ahead with a VBA solution.
 
Top