Data validation drop-down list dependent on value in another cell, otherwise lock the cell
I've been seeing a lot of posts regarding dependent lists using data validation, but in this situation, I am creating a dynamic timesheet. There are two columns with different kinds of work and sub-work I'm tracking (company and region), and then 20 other columns where people log their hours. If someone enters a company, only 2 of the remaining 20 columns in that row are meant to contain hours, and I am trying to lock the other cells while implementing a drop-down in the 2 columns (for that row). If someone enters a region in the next row, that row should be the reverse (lock the other 18 cells and allow the drop-down with the hours). This file is sent out weekly to fill out, and I'm trying to avoid using VBA if possible. Thanks!
microsoft-excel worksheet-function microsoft-excel-2010
asked Dec 28 '18 at 14:08
MollyMolly
1
add a comment |
I've been seeing a lot of posts regarding dependent lists using data validation, but in this situation, I am creating a dynamic timesheet. There are two columns with different kinds of work and sub-work I'm tracking (company and region), and then 20 other columns where people log their hours. If someone enters a company, only 2 of the remaining 20 columns in that row are meant to contain hours, and I am trying to lock the other cells while implementing a drop-down in the 2 columns (for that row). If someone enters a region in the next row, that row should be the reverse (lock the other 18 cells and allow the drop-down with the hours). This file is sent out weekly to fill out, and I'm trying to avoid using VBA if possible. Thanks!
microsoft-excel worksheet-function microsoft-excel-2010
asked Dec 28 '18 at 14:08
MollyMolly
1
What have you tried so far?
– cybernetic.nomad
Dec 28 '18 at 15:20
@Molly ,, locking Cells or Rows is much easier with VBA ,, you can set Data Validation also to lock Cells but has limited scope!!
– Rajesh S
Dec 29 '18 at 9:28
add a comment |
I've been seeing a lot of posts regarding dependent lists using data validation, but in this situation, I am creating a dynamic timesheet. There are two columns with different kinds of work and sub-work I'm tracking (company and region), and then 20 other columns where people log their hours. If someone enters a company, only 2 of the remaining 20 columns in that row are meant to contain hours, and I am trying to lock the other cells while implementing a drop-down in the 2 columns (for that row). If someone enters a region in the next row, that row should be the reverse (lock the other 18 cells and allow the drop-down with the hours). This file is sent out weekly to fill out, and I'm trying to avoid using VBA if possible. Thanks!
microsoft-excel worksheet-function microsoft-excel-2010
asked Dec 28 '18 at 14:08
MollyMolly
1
I've been seeing a lot of posts regarding dependent lists using data validation, but in this situation, I am creating a dynamic timesheet. There are two columns with different kinds of work and sub-work I'm tracking (company and region), and then 20 other columns where people log their hours. If someone enters a company, only 2 of the remaining 20 columns in that row are meant to contain hours, and I am trying to lock the other cells while implementing a drop-down in the 2 columns (for that row). If someone enters a region in the next row, that row should be the reverse (lock the other 18 cells and allow the drop-down with the hours). This file is sent out weekly to fill out, and I'm trying to avoid using VBA if possible. Thanks!
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
asked Dec 28 '18 at 14:08
MollyMolly
1
asked Dec 28 '18 at 14:08
MollyMolly
1
asked Dec 28 '18 at 14:08
MollyMolly
1
asked Dec 28 '18 at 14:08
MollyMolly
1
asked Dec 28 '18 at 14:08
MollyMolly
1
1
What have you tried so far?
– cybernetic.nomad
Dec 28 '18 at 15:20
@Molly ,, locking Cells or Rows is much easier with VBA ,, you can set Data Validation also to lock Cells but has limited scope!!
– Rajesh S
Dec 29 '18 at 9:28
add a comment |
What have you tried so far?
– cybernetic.nomad
Dec 28 '18 at 15:20
@Molly ,, locking Cells or Rows is much easier with VBA ,, you can set Data Validation also to lock Cells but has limited scope!!
– Rajesh S
Dec 29 '18 at 9:28
What have you tried so far?
– cybernetic.nomad
Dec 28 '18 at 15:20
What have you tried so far?
– cybernetic.nomad
Dec 28 '18 at 15:20
@Molly ,, locking Cells or Rows is much easier with VBA ,, you can set Data Validation also to lock Cells but has limited scope!!
– Rajesh S
Dec 29 '18 at 9:28
@Molly ,, locking Cells or Rows is much easier with VBA ,, you can set Data Validation also to lock Cells but has limited scope!!
– Rajesh S
Dec 29 '18 at 9:28
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1388460%2fdata-validation-drop-down-list-dependent-on-value-in-another-cell-otherwise-loc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1388460%2fdata-validation-drop-down-list-dependent-on-value-in-another-cell-otherwise-loc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What have you tried so far?
– cybernetic.nomad
Dec 28 '18 at 15:20
@Molly ,, locking Cells or Rows is much easier with VBA ,, you can set Data Validation also to lock Cells but has limited scope!!
– Rajesh S
Dec 29 '18 at 9:28