Data Validation not filling when adding row to table
up vote
1
down vote
favorite
I have a table I'm using to track issues. Two of the columns have data validation based on lists and there is conditional formatting set up on a few of the columns as well. When I add a new row to the table by typing something in the next row after the table ends, one of the data validations doesn't get added to the new row. The other data validation does get filled down and the conditional formatting all does as well. I can't figure out what's different about that column that is preventing it from automatically filling.
If I right click in a cell in the last row and select Insert Table Row Below, it does fill the validation, which is a decent workaround but since I'm used to just typing in the cell below to add rows to the table, it would be nice to have that work.
I can add screenshots if someone thinks it would help answer this but I'm hoping there's just some setting I'm not finding that is meant to cause this behavior.
microsoft-excel
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
asked Nov 12 '15 at 0:28
Desert Penguin
612
add a comment |
up vote
1
down vote
favorite
I have a table I'm using to track issues. Two of the columns have data validation based on lists and there is conditional formatting set up on a few of the columns as well. When I add a new row to the table by typing something in the next row after the table ends, one of the data validations doesn't get added to the new row. The other data validation does get filled down and the conditional formatting all does as well. I can't figure out what's different about that column that is preventing it from automatically filling.
If I right click in a cell in the last row and select Insert Table Row Below, it does fill the validation, which is a decent workaround but since I'm used to just typing in the cell below to add rows to the table, it would be nice to have that work.
I can add screenshots if someone thinks it would help answer this but I'm hoping there's just some setting I'm not finding that is meant to cause this behavior.
microsoft-excel
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
asked Nov 12 '15 at 0:28
Desert Penguin
612
The different is not between the columns, but the fact, that when you start typing in the first column, that row is not yet part of the table, so data validation isn't applied there. When you've entered the data into that cell, the table auto-expands, so both conditional formatting and data validation are applied. The only solution I can think of is to write a macro which always expands the table to the first empty row.
– Máté Juhász
Nov 12 '15 at 12:10
The column I typically type in is not one of the ones with Data Validation. They are both a few columns to the right. After I type in the name of the client in the first column, the table automatically expands to include the new row. One of the columns with Data Validation has that validation copied down to the new row in the table and the other one doesn't.
– Desert Penguin
Nov 13 '15 at 20:23
Have you tried re-creating that data valuation? Can you share an example?
– Máté Juhász
Nov 14 '15 at 5:25
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a table I'm using to track issues. Two of the columns have data validation based on lists and there is conditional formatting set up on a few of the columns as well. When I add a new row to the table by typing something in the next row after the table ends, one of the data validations doesn't get added to the new row. The other data validation does get filled down and the conditional formatting all does as well. I can't figure out what's different about that column that is preventing it from automatically filling.
If I right click in a cell in the last row and select Insert Table Row Below, it does fill the validation, which is a decent workaround but since I'm used to just typing in the cell below to add rows to the table, it would be nice to have that work.
I can add screenshots if someone thinks it would help answer this but I'm hoping there's just some setting I'm not finding that is meant to cause this behavior.
microsoft-excel
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
asked Nov 12 '15 at 0:28
Desert Penguin
612
I have a table I'm using to track issues. Two of the columns have data validation based on lists and there is conditional formatting set up on a few of the columns as well. When I add a new row to the table by typing something in the next row after the table ends, one of the data validations doesn't get added to the new row. The other data validation does get filled down and the conditional formatting all does as well. I can't figure out what's different about that column that is preventing it from automatically filling.
If I right click in a cell in the last row and select Insert Table Row Below, it does fill the validation, which is a decent workaround but since I'm used to just typing in the cell below to add rows to the table, it would be nice to have that work.
I can add screenshots if someone thinks it would help answer this but I'm hoping there's just some setting I'm not finding that is meant to cause this behavior.
microsoft-excel
microsoft-excel
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
asked Nov 12 '15 at 0:28
Desert Penguin
612
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
asked Nov 12 '15 at 0:28
Desert Penguin
612
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
edited Nov 12 '15 at 2:46
Michael Frank
6,00912642
6,00912642
asked Nov 12 '15 at 0:28
Desert Penguin
612
asked Nov 12 '15 at 0:28
Desert Penguin
612
asked Nov 12 '15 at 0:28
Desert Penguin
612
612
The different is not between the columns, but the fact, that when you start typing in the first column, that row is not yet part of the table, so data validation isn't applied there. When you've entered the data into that cell, the table auto-expands, so both conditional formatting and data validation are applied. The only solution I can think of is to write a macro which always expands the table to the first empty row.
– Máté Juhász
Nov 12 '15 at 12:10
The column I typically type in is not one of the ones with Data Validation. They are both a few columns to the right. After I type in the name of the client in the first column, the table automatically expands to include the new row. One of the columns with Data Validation has that validation copied down to the new row in the table and the other one doesn't.
– Desert Penguin
Nov 13 '15 at 20:23
Have you tried re-creating that data valuation? Can you share an example?
– Máté Juhász
Nov 14 '15 at 5:25
add a comment |
The different is not between the columns, but the fact, that when you start typing in the first column, that row is not yet part of the table, so data validation isn't applied there. When you've entered the data into that cell, the table auto-expands, so both conditional formatting and data validation are applied. The only solution I can think of is to write a macro which always expands the table to the first empty row.
– Máté Juhász
Nov 12 '15 at 12:10
The column I typically type in is not one of the ones with Data Validation. They are both a few columns to the right. After I type in the name of the client in the first column, the table automatically expands to include the new row. One of the columns with Data Validation has that validation copied down to the new row in the table and the other one doesn't.
– Desert Penguin
Nov 13 '15 at 20:23
Have you tried re-creating that data valuation? Can you share an example?
– Máté Juhász
Nov 14 '15 at 5:25
The different is not between the columns, but the fact, that when you start typing in the first column, that row is not yet part of the table, so data validation isn't applied there. When you've entered the data into that cell, the table auto-expands, so both conditional formatting and data validation are applied. The only solution I can think of is to write a macro which always expands the table to the first empty row.
– Máté Juhász
Nov 12 '15 at 12:10
The different is not between the columns, but the fact, that when you start typing in the first column, that row is not yet part of the table, so data validation isn't applied there. When you've entered the data into that cell, the table auto-expands, so both conditional formatting and data validation are applied. The only solution I can think of is to write a macro which always expands the table to the first empty row.
– Máté Juhász
Nov 12 '15 at 12:10
The column I typically type in is not one of the ones with Data Validation. They are both a few columns to the right. After I type in the name of the client in the first column, the table automatically expands to include the new row. One of the columns with Data Validation has that validation copied down to the new row in the table and the other one doesn't.
– Desert Penguin
Nov 13 '15 at 20:23
The column I typically type in is not one of the ones with Data Validation. They are both a few columns to the right. After I type in the name of the client in the first column, the table automatically expands to include the new row. One of the columns with Data Validation has that validation copied down to the new row in the table and the other one doesn't.
– Desert Penguin
Nov 13 '15 at 20:23
Have you tried re-creating that data valuation? Can you share an example?
– Máté Juhász
Nov 14 '15 at 5:25
Have you tried re-creating that data valuation? Can you share an example?
– Máté Juhász
Nov 14 '15 at 5:25
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
Select the column that you're applying the data validation to. Make sure to select the entire column by clicking on the letter of the column, not by selecting a cell range.
Typically, in a table, when you first select the column, it highlights only the data section of the table. (For example, if you're selecting column C of a 12-row table with headers, when you click on column C itself, a black box is drawn around C2-C12, because Excel knows C1 is a header and that you don't actually need C13+ for your table. It thinks it's helping by only selecting this "meat" of your table.) If you apply data validation at this point, then add a row of data to your table in C13, you won't have your data validation auto-populate because you've (unknowingly) only selected C2-C12.
Instead, click on column C again. Now your selection black box includes C1 and continues indefinitely down the page. Now apply data validation. When you enter data in row C13 (or C14, or C15, etc.), you will always have your data validation auto-populate into the table. I hope this makes sense!
edited Dec 29 '15 at 16:54
karel
9,17793138
answered Dec 29 '15 at 16:44
Kate K
1
add a comment |
up vote
-1
down vote
I looked for different options, and for those who would like to "keep it clean" wouldn't like to select the complete Column letter, because else the first Row (header row) will show as if it is not meeting the right criteria.
So what I did was:
- Select the complete column letter, then "Data Validation" and select "Any Value"
- Select your column data (selecting the column close to the header row), this will only select your table's column, then "Data Validation" and select your validations as desired
- Add a new row by typing below the last available row of the table and the validation will now be inherited :)
Enjoy!
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
answered Oct 11 '17 at 0:23
Rodo
1
add a comment |
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',
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%2f999326%2fdata-validation-not-filling-when-adding-row-to-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Select the column that you're applying the data validation to. Make sure to select the entire column by clicking on the letter of the column, not by selecting a cell range.
Typically, in a table, when you first select the column, it highlights only the data section of the table. (For example, if you're selecting column C of a 12-row table with headers, when you click on column C itself, a black box is drawn around C2-C12, because Excel knows C1 is a header and that you don't actually need C13+ for your table. It thinks it's helping by only selecting this "meat" of your table.) If you apply data validation at this point, then add a row of data to your table in C13, you won't have your data validation auto-populate because you've (unknowingly) only selected C2-C12.
Instead, click on column C again. Now your selection black box includes C1 and continues indefinitely down the page. Now apply data validation. When you enter data in row C13 (or C14, or C15, etc.), you will always have your data validation auto-populate into the table. I hope this makes sense!
edited Dec 29 '15 at 16:54
karel
9,17793138
answered Dec 29 '15 at 16:44
Kate K
1
add a comment |
up vote
0
down vote
Select the column that you're applying the data validation to. Make sure to select the entire column by clicking on the letter of the column, not by selecting a cell range.
Typically, in a table, when you first select the column, it highlights only the data section of the table. (For example, if you're selecting column C of a 12-row table with headers, when you click on column C itself, a black box is drawn around C2-C12, because Excel knows C1 is a header and that you don't actually need C13+ for your table. It thinks it's helping by only selecting this "meat" of your table.) If you apply data validation at this point, then add a row of data to your table in C13, you won't have your data validation auto-populate because you've (unknowingly) only selected C2-C12.
Instead, click on column C again. Now your selection black box includes C1 and continues indefinitely down the page. Now apply data validation. When you enter data in row C13 (or C14, or C15, etc.), you will always have your data validation auto-populate into the table. I hope this makes sense!
edited Dec 29 '15 at 16:54
karel
9,17793138
answered Dec 29 '15 at 16:44
Kate K
1
add a comment |
up vote
0
down vote
up vote
0
down vote
Select the column that you're applying the data validation to. Make sure to select the entire column by clicking on the letter of the column, not by selecting a cell range.
Typically, in a table, when you first select the column, it highlights only the data section of the table. (For example, if you're selecting column C of a 12-row table with headers, when you click on column C itself, a black box is drawn around C2-C12, because Excel knows C1 is a header and that you don't actually need C13+ for your table. It thinks it's helping by only selecting this "meat" of your table.) If you apply data validation at this point, then add a row of data to your table in C13, you won't have your data validation auto-populate because you've (unknowingly) only selected C2-C12.
Instead, click on column C again. Now your selection black box includes C1 and continues indefinitely down the page. Now apply data validation. When you enter data in row C13 (or C14, or C15, etc.), you will always have your data validation auto-populate into the table. I hope this makes sense!
edited Dec 29 '15 at 16:54
karel
9,17793138
answered Dec 29 '15 at 16:44
Kate K
1
Select the column that you're applying the data validation to. Make sure to select the entire column by clicking on the letter of the column, not by selecting a cell range.
Typically, in a table, when you first select the column, it highlights only the data section of the table. (For example, if you're selecting column C of a 12-row table with headers, when you click on column C itself, a black box is drawn around C2-C12, because Excel knows C1 is a header and that you don't actually need C13+ for your table. It thinks it's helping by only selecting this "meat" of your table.) If you apply data validation at this point, then add a row of data to your table in C13, you won't have your data validation auto-populate because you've (unknowingly) only selected C2-C12.
Instead, click on column C again. Now your selection black box includes C1 and continues indefinitely down the page. Now apply data validation. When you enter data in row C13 (or C14, or C15, etc.), you will always have your data validation auto-populate into the table. I hope this makes sense!
edited Dec 29 '15 at 16:54
karel
9,17793138
answered Dec 29 '15 at 16:44
Kate K
1
edited Dec 29 '15 at 16:54
karel
9,17793138
edited Dec 29 '15 at 16:54
karel
9,17793138
edited Dec 29 '15 at 16:54
karel
9,17793138
9,17793138
answered Dec 29 '15 at 16:44
Kate K
1
answered Dec 29 '15 at 16:44
Kate K
1
answered Dec 29 '15 at 16:44
Kate K
1
1
add a comment |
add a comment |
up vote
-1
down vote
I looked for different options, and for those who would like to "keep it clean" wouldn't like to select the complete Column letter, because else the first Row (header row) will show as if it is not meeting the right criteria.
So what I did was:
- Select the complete column letter, then "Data Validation" and select "Any Value"
- Select your column data (selecting the column close to the header row), this will only select your table's column, then "Data Validation" and select your validations as desired
- Add a new row by typing below the last available row of the table and the validation will now be inherited :)
Enjoy!
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
answered Oct 11 '17 at 0:23
Rodo
1
add a comment |
up vote
-1
down vote
I looked for different options, and for those who would like to "keep it clean" wouldn't like to select the complete Column letter, because else the first Row (header row) will show as if it is not meeting the right criteria.
So what I did was:
- Select the complete column letter, then "Data Validation" and select "Any Value"
- Select your column data (selecting the column close to the header row), this will only select your table's column, then "Data Validation" and select your validations as desired
- Add a new row by typing below the last available row of the table and the validation will now be inherited :)
Enjoy!
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
answered Oct 11 '17 at 0:23
Rodo
1
add a comment |
up vote
-1
down vote
up vote
-1
down vote
I looked for different options, and for those who would like to "keep it clean" wouldn't like to select the complete Column letter, because else the first Row (header row) will show as if it is not meeting the right criteria.
So what I did was:
- Select the complete column letter, then "Data Validation" and select "Any Value"
- Select your column data (selecting the column close to the header row), this will only select your table's column, then "Data Validation" and select your validations as desired
- Add a new row by typing below the last available row of the table and the validation will now be inherited :)
Enjoy!
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
answered Oct 11 '17 at 0:23
Rodo
1
I looked for different options, and for those who would like to "keep it clean" wouldn't like to select the complete Column letter, because else the first Row (header row) will show as if it is not meeting the right criteria.
So what I did was:
- Select the complete column letter, then "Data Validation" and select "Any Value"
- Select your column data (selecting the column close to the header row), this will only select your table's column, then "Data Validation" and select your validations as desired
- Add a new row by typing below the last available row of the table and the validation will now be inherited :)
Enjoy!
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
answered Oct 11 '17 at 0:23
Rodo
1
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
edited Oct 11 '17 at 0:33
Appleoddity
6,88621024
6,88621024
answered Oct 11 '17 at 0:23
Rodo
1
answered Oct 11 '17 at 0:23
Rodo
1
answered Oct 11 '17 at 0:23
Rodo
1
1
add a comment |
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f999326%2fdata-validation-not-filling-when-adding-row-to-table%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
The different is not between the columns, but the fact, that when you start typing in the first column, that row is not yet part of the table, so data validation isn't applied there. When you've entered the data into that cell, the table auto-expands, so both conditional formatting and data validation are applied. The only solution I can think of is to write a macro which always expands the table to the first empty row.
– Máté Juhász
Nov 12 '15 at 12:10
The column I typically type in is not one of the ones with Data Validation. They are both a few columns to the right. After I type in the name of the client in the first column, the table automatically expands to include the new row. One of the columns with Data Validation has that validation copied down to the new row in the table and the other one doesn't.
– Desert Penguin
Nov 13 '15 at 20:23
Have you tried re-creating that data valuation? Can you share an example?
– Máté Juhász
Nov 14 '15 at 5:25