need to stop users from editing data in a column but not if blank

[sumhungl0]

i have a sheet that gets reviewed by several people and they make changes to correct their data. the sheet has column A & B as an identification code for their data, like a compound numbering system.
example data is like this: cell A2 has "L1", B2 has "605", A3 has "L1", B3 has "609", A500 has "L2", B500 has "609".
column A is a two digit alpha-numeric code that identifies the group and column B is four digit number that identifies the item in that group from column A. so it is a two part identification. hope that is clear, I don't know how else to explain.

the problem I am having is that people are changing those two columns and then I have to figure out what it was before. so what I need is to keep people from changing those id columns but there is a catch. if there is a blank in the id columns (columns A & B), then they do need to fill it in. and another problem, sometimes they input a group code in column A and then its number in column B and they use a number that has already been used for that grouping.

so here is what im trying to do:
1. keep people from editing pre-filled data in columns A & B, but allow them to input data if blank
2. keep people from duplicating numbers in column B if that number has already been used for that group

can anyone help me with this? thanks in advance.

 

[ask2tsp]

I would like to help you with this matter.
Before diving in I have a few questions.

Where are the blank cells located. Anywhere in columns A or B, or in a restricted number of rows?

After a user has written a value into an empty cell it is not empty anymore. Does that mean that it is not editable anymore or should the cell be flagged to remain open for changes?

Other validations? While we're at it, should there be other things to check? For example looking up a group code from a list of valid group codes or checking it is a capital letter followed by one number.

After you answer these questions I will try to make a start to get you going in the right direction.

 

[sumhungl0]

Where are the blank cells located. Anywhere in columns A or B, or in a restricted number of rows?

yes, the only blank cells that should have restriction should be in columns A &/or B. other columns have data that users will change/update. the issue is that i need to protect values in A & B if not blank. problem is that users sometimes change these identifying columns and then i do not know what row/id they edited/updated.

After a user has written a value into an empty cell it is not empty anymore. Does that mean that it is not editable anymore or should the cell be flagged to remain open for changes?

maybe you can help me with this.... this is a single excel file that will be updated by the users and then emailed back to me to input into a web database(not so user friendly, old and outdated). i then have to input the updates from the users going off of the excel sheet they send me.
to answer your question, i think the blank cell in A or B that they fill in/update should remain unprotected. in case they want to change it again. your thoughts? if it should happen to duplicate what another user sends me in their version of the updates/edits then i will just have to adjust them. i think that situation would be something that would apply to a shared sheet where multiple users edit/update the same sheet. that is not the case for me. what do you think?

Other validations? While we're at it, should there be other things to check? For example looking up a group code from a list of valid group codes or checking it is a capital letter followed by one number.

ok for this question, i cannot think of anything else i might need to check for. all i need to do is make sure the users do not use an id that has already been used. that they do not duplicate identification of a row/record. as far as the identification system maybe i need to explain a bit more. column A is the group. it can be letters, numbers or both. does not need to be specifically a letter and then a number. it can be just letters or just numbers as well, but it is only two digits. column B is just numbers and it is four digits long. it basically just identifies the item number of the group.

i hope this helps, and thank you for your reply.

 

[sumhungl0]

maybe i need to fill you in on what i am dealing with.

a user just sent me his edited/updated sheet. he updated identification values in columns A and B on several records/rows. he then on another day updated those same identification values again and im having problems figuring out what records/rows he was actually trying to update. i tried to use excels change tracking and it just gets very confusing because he changed values of cells several times. and he did this on over 50 records/rows. i have been going back and forth comparing the original sheet and trying to figure out each time each cell was changed to finally get to the value that i need to update in the web database. he was trying to change the identification of the record or change the grouping but as soon as that is changed it makes it hard for me to figure out what record i need to update in the database. i need to protect the integrity of the identification columns(A & B) and make sure the user does not use identification that has already been used.

maybe that will help.

 

[ask2tsp]

the only blank cells that should have restriction should be in columns A &/or B.

That part (the columns) was clear to me. The question was about the rows. I guess only blank cells in the range row 2 to last row with non blank cells, not below that row.

this is a single excel file that will be updated by the users and then emailed back to me

Who / what builds this excel file? I ask this because we want to sent to the the user an excel file with data plus edit rules. This can be done in two different ways.
1. Start with an excel file with all the edit rules in place and then add the data that the user has to complete. or 2. First build the exel file (as it works now) and then add the edit rules to it.

what another user sends me in their version of the updates/edits

I assume every user gets a file with their portion of lines in it? Or do they all get the total package, they only fill in the rows they are concerned with?
I ask this for the unique / duplicates issue. If every user gets their share of the data we only have to check the user edited group/id columns for uniqueness, else all the lines.
Let's not go the shared sheet road. The goal of this is to save you the time to figure out what they changed by blocking what they should not change.
----------
I know, it looks a bit overwhelming, all these questions and answers going back and forth. It is the design that must be more or less accurate before we start building fancy formula's, validation rules and/or vba code. Otherwise we risk putting a lot of effort building something that is not quite what was needed.

 

[sumhungl0]

That part (the columns) was clear to me. The question was about the rows. I guess only blank cells in the range row 2 to last row with non blank cells, not below that row.

Who / what builds this excel file? I ask this because we want to sent to the the user an excel file with data plus edit rules. This can be done in two different ways.
1. Start with an excel file with all the edit rules in place and then add the data that the user has to complete. or 2. First build the exel file (as it works now) and then add the edit rules to it.

I assume every user gets a file with their portion of lines in it? Or do they all get the total package, they only fill in the rows they are concerned with?
I ask this for the unique / duplicates issue. If every user gets their share of the data we only have to check the user edited group/id columns for uniqueness, else all the lines.
Let's not go the shared sheet road. The goal of this is to save you the time to figure out what they changed by blocking what they should not change.
----------
I know, it looks a bit overwhelming, all these questions and answers going back and forth. It is the design that must be more or less accurate before we start building fancy formula's, validation rules and/or vba code. Otherwise we risk putting a lot of effort building something that is not quite what was needed.

sorry, I been off for a bit.
1. yes I only need to protect columns A &/or B but only if values exist.
2. it is a report from an outdated web database. basically I get the report on screen and copy/paste to excel for the users to make their updates. they email their copy back and I start inputting their updates back into the database. the whole process sux. the problem here is when users want to update the identification of the records/rows by editing existing values in A & B. then I don't know what record in the database I need to update because they have changed the record identification/numbering system. as far as your two options above(1 & 2), I do not mind either way, I am the one who creates this excel workbook. what would you do, or what do you think would be better/easier?
3. no, each user is sent the same file in a mass email. I just put in filters so they can narrow it down to their section. but yes they do not make edits to any records that are not theirs and if they do I disregard or call them on it.

hope this helps, sorry for the delay. and thanks again.

 

[Eric W]

You have a couple of tricky requirements.

so here is what im trying to do:
1. keep people from editing pre-filled data in columns A & B, but allow them to input data if blank
2. keep people from duplicating numbers in column B if that number has already been used for that group

For #1, about the best idea I can come up with is to select all the cells on the sheet, then Format Cells, then uncheck the Locked box on the protection tab. Then go to columns A and B and check the box for all cells with data in them. (This can be automated with a macro.) Then protect the entire sheet. Then create a WorksheetChange event that monitors columns A and B, and if data is entered in a cell then it will lock the cell, preventing it from being changed. A bit of a hassle, but it should work. Protecting the sheet could have other undesired effects though.

Another possibility is to create a duplicate sheet for your first sheet. The Worksheet change event macro will monitor the sheet, and replicate any changes to columns A and B to the duplicate sheet. But if the duplicate sheet already has a value in cell B100, then it will put that value back in the main sheet and display a stern warning! The duplicate sheet could be hidden so your users won't see it.

#2 is a bit easier. Select column B. Then from the Data tab, click Data Validation, Allow: Custom, and enter this formula:
=COUNTIFS($A$1:$A$100,A1,$B$1:$B$100,B1)<=1

Adjust the ranges to match your sheet. This will prevent them from entering a duplicate number for a group in column B.

If you want, you can also put a Data Validation rule on column A, which requires anything entered in column A to be exactly 2 digits. This should help prevent them from mixing up the A and B columns.

Let me know if you need details on any of this.