Validating data in layers?

aanthony

New Member
Joined
Aug 13, 2007
Messages
1
Greetings,

I have an excel raw data capture spreadsheet containing 8 of the following formats in which readers must input values for columns 6 to 1:
6 5 4 3 2 1
A
B
C
D
E
F
G
H
10 11 12 13 14 15

the column headers and footers should not be changed by the user. Currently, we have a system were all cells are unprotected and cells from A6 through H1 (48 cells) have a validation that accepts a defined range of values from a drop-down list. We have been having issues lately with "no entries - i.e. blank cells" and users actually typing over the column headers, footers and the row identifiers. This creates problems later on in our data analysis. In addition, we have tried to protect the cells that we do not want altered previously. However, as we use the same data form and input new values for the column footers each time, we had to protect and unprotect the cells for each new data collection event. We have tried to validate in layers and have found out that this is also not possible. How can we protect our sheet identifiers while allowing users to enter values within a defined range and not accept blank cell entries? Any help is much appreciated.

Regards,
Alissa
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Exceptions to Worksheet Protections

Select the range that you want users to have the ability to edit.
then, under "Tools", select "Protection" and "Allow Users to Edit Ranges".
Click 'New' (Range) and the New Range Dialog Box opens, where you can re-name the range, re-define the range, and can (optionally) specify a password and permissions for what the different user groups can do to the defined range.
Click OK and then go ahead and protect your workbook as you have previously.

The protection you just implemented will apply to everything
except the range you just defined before the protection was set.

Goos Luck!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top