Forum Discussion
How to set Data Validation for VLOOKUP and row dependent formulas?
Hi, I'd like to use data validation to effectively 'lock' my formulas in place, so that any accidental edits to a cell that don't align with the correct formula for that column will be flagged. However, my formulas are row dependent, and so far I can only find a way of setting a formula data validation for a specific row number.
For example, all of the columns in my sheet use a variation of this formula:
=VLOOKUP(B2,'Sheet 1'!A:B,2,FALSE)
Where the B column is dependent on the row number, so for row 3 of this column the formula is =VLOOKUP(B3,'Sheet 1'!A:B,2,FALSE), and so on.
Can I use data validation to lock in the formula for each row, when the formula is row dependent?
I have over 800 rows, so can't set data validation for each cell individually!
Thank you.
1 Reply
I don't think you can use Data Validation for that purpose.
- Select the cells that the users should be able to populate/edit.
- Press Ctrl+1 to activate the Format Cells dialog.
- Activate the Protection tab.
- Clear the Locked check box.
- Click OK.
- Activate the Review tab of the ribbon.
- Click Protect Sheet.
- If you wish, enter a password that will be needed if you want to unprotect the sheet later on. Don't forget the password!
- Click OK. If you provided a password, you'll have to enter it again as confirmation.
- Save the workbook.
