Forum Discussion
Allow data Input but don't allow Edit
Method number 3 seems like the best option for me. VBA unfortunately is not an option since the people using the excel are on an iPad and macros is not supported on the iPad (at least that I'm aware of). I'm having trouble getting data validation to work though because I can get it to prompt me that I can't edit the cell, but I get the same message even for cell that are empty. Not sure if I'm doing anything wrong but I copy pasted the exact formula you gave me.
Instead of using a static reference like =ISBLANK(A1), we need to ensure that the formula applies dynamically to all cells in the range.
Select the entire range where users will input data (e.g., A1:A100).
Go to Data > Data Validation > Data Validation.
Set the Allow option to Custom.
Use the formula: =ISBLANK(A1). The key is to select the correct starting point (e.g., A1), and Excel will apply this rule to each cell in the selected range.
(NOTE:- Adjust the Cell's according to your sheet and requirement.)
Please also make sure you don't have extra data validation rules applied on blank cells that are not intended for input. You can clear any previous data validation applied incorrectly by selecting the entire worksheet and choosing Clear All from the Data Validation menu, then reapplying to the desired range.
For Clarity suggest to use Conditional Formatting:
Select the input range (e.g., A1:A100).
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter the formula: =NOT(ISBLANK(A1)).
Set a fill color to make it visually clear which cells are already filled.
Things to Remember:
When applying data validation and conditional formatting, ensure you're selecting the entire range.
The reference (A1) will adjust dynamically for each cell
This method only gives a warning. Users can still ignore the warning and overwrite the data. If stronger protection is needed, consider protecting the worksheet with locked cells
I really don't know what I'm doing wrong, I am following all the steps you provided. Like mentioned above I did conditional formatting to make sure blanks are empty and when they are filled.
Conditional formatting is:
I know it's working because on the sheet its highlighting correctly.
The issue I'm having is I can't type in 6 on A6 after I setup the data validation. These are the current settings I have on Data validation screen.
Whenever I try and type in anything on A6 this is the error I get.
Again, not sure what I am doing wrong is whatever I'm trying to do is just not possible.