Hello,
I am very new to Excel and have never ventured into VBA (though I plan to do so soon). I'm creating a 2003 file for my job that has a lot of computer-unfriendly people. So I've implemented a wide variety of formulas that reference each other (to minimize the amount of data they have to enter). After finishing just about everything, I ran into one problem:
I have two sheets (one called "Active Students" and another called "Testers"), and I need to lock an individual cell where staff input a test score in the "Active Students" sheet when a value exists in a specific cell in the "Testers" sheet and then unlocked when the value is not there.
Because these tables have to be sorted by the staff, I can't protect the sheet (protecting it makes me unable to sort no matter what I try). So in order to protect the formulas in certain columns, I have been using data validation with "}" in the formula so that they get an error message if they accidentally type over the formula (not perfect, but it does the job).
Ideally, I would be able to write a formula saying "if data exists in "x" cell, then create data validation in y cell, if data does not exist in "x" cell, then clear data validation in "y" cell."
Right now, I have the following formula in the cell:
=IF(A4>=1,VLOOKUP((A4:A27,'Active Students'!$A$4:$P$48,7,FALSE),"")
I'm doing this to reference the "Active Students" data to the "Testers" sheet via the Sudent ID #. What I need is to create and clear data validation in one cell (found through their Student ID#) if there is any data on the same Student ID# row or cell in the "Testers" sheet.
I hope this makes sense, and I hope this is even possible. Thank you for your time.
I am very new to Excel and have never ventured into VBA (though I plan to do so soon). I'm creating a 2003 file for my job that has a lot of computer-unfriendly people. So I've implemented a wide variety of formulas that reference each other (to minimize the amount of data they have to enter). After finishing just about everything, I ran into one problem:
I have two sheets (one called "Active Students" and another called "Testers"), and I need to lock an individual cell where staff input a test score in the "Active Students" sheet when a value exists in a specific cell in the "Testers" sheet and then unlocked when the value is not there.
Because these tables have to be sorted by the staff, I can't protect the sheet (protecting it makes me unable to sort no matter what I try). So in order to protect the formulas in certain columns, I have been using data validation with "}" in the formula so that they get an error message if they accidentally type over the formula (not perfect, but it does the job).
Ideally, I would be able to write a formula saying "if data exists in "x" cell, then create data validation in y cell, if data does not exist in "x" cell, then clear data validation in "y" cell."
Right now, I have the following formula in the cell:
=IF(A4>=1,VLOOKUP((A4:A27,'Active Students'!$A$4:$P$48,7,FALSE),"")
I'm doing this to reference the "Active Students" data to the "Testers" sheet via the Sudent ID #. What I need is to create and clear data validation in one cell (found through their Student ID#) if there is any data on the same Student ID# row or cell in the "Testers" sheet.
I hope this makes sense, and I hope this is even possible. Thank you for your time.