Using Data validation to prevent certain letters and numbers within a cell

Question

Morning,
Excel is alittle new to me, ive tried to find a way to do this without help but i thought id ask before i give up aha.

Im trying to prevent a certain letter and numbers within a larger line from being entered. i.e below

1 |EXTU48506 -prevent
2 |EXTU48006 - ok
3 |EXTU90386 -prevent
4 |EXTU81584 -ok

What im trying to do is prevent the the extu48506 and extu90386 from entering into that column as that number is already destined to be in another column, i dont want a repeat when it comes to data entry is there a way to utilise Data validation to prevent the 485 and the 903 from being allowed into those cells or that column without affecting other numbers and letters.

Answer 1

Sorry i forgot to update it, was in abit of a rush this morning.

https://1drv.ms/x/s!AgjLj62E4Ryigo5Y3D-iBk3hUid9LQ
this is the latest one, similar thing again.

Answer 2

Okay, I can see "EXTU48506" etc. in column C in your file, but there is no range "Words" in that file where the codes are listed.

Please update the file and give me a sign.

Andreas.

Answer 3

Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range If Not Intersect(Target, Range("C:C")) Is Nothing Then For Each c In Intersect(Target, Range("C:C")) If Not IsError(Application.Match(c.Value, Range("Words"), 0)) Then With Application .EnableEvents = False .Undo MsgBox "That's a forbidden word - try another." .EnableEvents = True Exit Sub End With End If Next c End If End Sub

This was something i came across, but due to my no skills, i cant figure out how to suit it to my needs,

ive since created a range on a new spreadsheet for "words" listed with the with the below characters i wish to not allow in column C but literally nothing happens aha

Answer 4

https://1drv.ms/x/s!AgjLj62E4Ryigo5V9gAQvGf4GpVmyw?e=SVeaXL
ive removed the unnecessary sheets and hidden stuff that bares no use and commented out the existing vba lines require to suit those missing sheets.

In the sheet ive attached you can see the numbers letters and numbers in bold that ive trying to prevent from showing in the "Base" column as that cannot be allowed, the those figures can show in the "top" section but just not the base, all i was wanting to do is once they're added to throw up an error instructing the user that it cannot be done any why.

i havent tried anything yet with VBA to restrict those yet as im not at all proficent with excel and only started a week ago.

Answer 5

That is not possible using a data validation, it might be possible using VBA.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.