Serial Locking

%3CLINGO-SUB%20id%3D%22lingo-sub-1922760%22%20slang%3D%22en-US%22%3ESerial%20Locking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922760%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20array%20of%20data%20in%20a%20column%20in%20the%20form%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%2287%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%3E1%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E9%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E10%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2F20-21%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20lock%20the%20column%20so%20that%20it%20only%20takes%20values%20next%20in%20the%20serial%2C%20for%20example%2C%20the%20next%20value%20should%20only%20be%20taken%20as%2014%2F20-21.%20I%20need%20to%20lock%20the%20column%20so%20that%20it%20doesn't%20take%20any%20other%20value%20apart%20from%20the%20next%20number%20in%20the%20serial%20on%20the%20left%20side%20(%20%2F20-21%20stays%20common).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1922760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1922940%22%20slang%3D%22de-DE%22%3ESubject%3A%20Serial%20Locking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1922940%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878470%22%20target%3D%22_blank%22%3E%40SG_1996515%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20some%20situations%2C%20and%20why%20not%20with%20array%20functions%2C%20the%20use%20of%20the%20blade%20guard%20may%20be%20undesirable.%20In%20order%20to%20protect%20cells%20with%20formulas%20or%20any%20other%20cells%20from%20unwanted%20changes%20in%20these%20cases%2C%20the%20following%20procedure%20can%20be%20used.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20Format%20cells%20%2F%20Protection%20command%20to%20define%20which%20cells%20should%20be%20provided%20with%20cell%20protection%20and%20which%20should%20not.%20To%20remove%20the%20cell%20protection%2C%20simply%20remove%20the%20tick%20when%20locked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20desired%20cell%20range%20or%20all%20cells%20in%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20open%20the%20corresponding%20dialog%20window%20via%20the%20menu%20Data%20%2F%20Data%20tools%20%2F%20Data%20verification%20%2F%20Data%20verification.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnder%20Allow%2C%20select%20the%20Custom%20entry%20and%20enter%20the%20following%20formula%3A%20%3D%20NOT%20(CELL%20(%22Protection%22%3B%20A1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20a%20click%20on%20the%20OK%20button%2C%20the%20defined%20cells%20are%20now%20protected%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EImportant%3A%20%3CBR%20%2F%3E%20The%20procedure%20presented%20protects%20the%20cells%20from%20accidentally%20overwriting%20the%20cells.%20However%2C%20the%20locked%20cell%20can%20still%20be%20deleted%20with%20the%20delete%20key.%20Protected%20cells%20can%20also%20be%20overwritten%20using%20the%20copy%20and%20paste%20function.%20The%20function%20presented%20is%20therefore%20not%20a%20particularly%20good%20protection%2C%20but%20is%20sufficient%20in%20practice%20in%20many%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAdditional%20Info%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EVBA%20Excel%20-%20How%20to%20lock%20specific%20cells%20but%20allow%20filtering%20and%20sorting%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverrun.com%2Fde%2Fq%2F2689186%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverrun.com%2Fde%2Fq%2F2689186%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have an array of data in a column in the form,

 

1/20-21
2/20-21
3/20-21
4/20-21
5/20-21
6/20-21
7/20-21
8/20-21
9/20-21
10/20-21
11/20-21
12/20-21
13/20-21

 

 

I would like to lock the column so that it only takes values next in the serial, for example, the next value should only be taken as 14/20-21. I need to lock the column so that it doesn't take any other value apart from the next number in the serial on the left side ( /20-21 stays common). 

 

Can someone help?

2 Replies

@SG_1996515 

In some situations, and why not with array functions, the use of the blade guard may be undesirable. In order to protect cells with formulas or any other cells from unwanted changes in these cases, the following procedure can be used.

 

Use the Format cells / Protection command to define which cells should be provided with cell protection and which should not. To remove the cell protection, simply remove the tick when locked.

 

Select the desired cell range or all cells in the worksheet.

 

Now open the corresponding dialog window via the menu Data / Data tools / Data verification / Data verification.

 

Under Allow, select the Custom entry and enter the following formula: = NOT (CELL ("Protection"; A1))

 

With a click on the OK button, the defined cells are now protected as desired.

 

Important:
The procedure presented protects the cells from accidentally overwriting the cells. However, the locked cell can still be deleted with the delete key. Protected cells can also be overwritten using the copy and paste function. The function presented is therefore not a particularly good protection, but is sufficient in practice in many cases.

 

 

Additional Info:

VBA Excel - How to lock specific cells but allow filtering and sorting

https://stackoverrun.com/de/q/2689186

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

@SG_1996515 

Since your data set is neither Number nor Date , are a uncommon/customize list then the  best I would like to suggest is,, in any blank range  create list from 1/20-21 to 100/20-21 or which ever is suitable to you. 

Select the data range where you want to enter Serial no ( here is AF2:A16), then Go to Data Validation and for Criteria, select the LIST, and select the data range (here is Y2:Y16) where you have stored your custom list,, and finish with Ok.

 

Rajesh-S_0-1606129094706.png

Another should use this formula, for data Validation as CUSTOM option, will  help you to maintain UNIQUE Serial number list and will check and validate first 2 digits only,,, and allow the pattern /20-21 with all.

=COUNTIF($A$2:$A$100, A2)<=1

Note: 

  • Range A2:A100 where you want to enter Serial number.   
  • Start with 1/20-21 in A2 and continue.