Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How do I validate data from another validation cell?
I am currently working on a project that I can't figure out the last few
details. This is similar to what I have: Program: (followed by validated list of programs as a dropdown) Features: (followed by validated list of features as a dropdown) Table in another tab: Program Feature Word Table of Contents Excel Pivot Table Both Save Both Print I would like to limit the Features validation to only display features that are in the program the user chose (i.e., If they chose Word as the program, it should only show Table of Contents, Save, and Print in the dropdown). Any ideas on this? |
#2
|
|||
|
|||
Hi Bo
check out http://www.contextures.com/xlDataVal02.html -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bo" wrote in message ... I am currently working on a project that I can't figure out the last few details. This is similar to what I have: Program: (followed by validated list of programs as a dropdown) Features: (followed by validated list of features as a dropdown) Table in another tab: Program Feature Word Table of Contents Excel Pivot Table Both Save Both Print I would like to limit the Features validation to only display features that are in the program the user chose (i.e., If they chose Word as the program, it should only show Table of Contents, Save, and Print in the dropdown). Any ideas on this? |
#3
|
|||
|
|||
That has some great information that could work, however, if possible I want
to go one or two steps further. The example showed fruit in two categories. My categories are combined. Using the example on the site, let me restate the question. Let's say I have a list of the food groups (i.e. meats, dairy, fruits/veggies, etc.) and specific foods for each one listed in one large column. For each item of food, a second column states what food group it is in. This is done in my data tab. Now I go to my main tab and have a validated cell to select what food group I want to look at. I want a second validated cell to only show the foods in the food group. Now let's say all the foods in the data cell are alphabetical, so it is not possible to select only the fruits and name the column Fruits. How would I validate the second cell to show only the food items from the selected group? And if possible, one step further would be to note that maybe a food item could be classified as two different groups. Just for this example, let's say yogurt is both dairy and fruit (having milk products and fruit). Recognizing this, I change the food group column to hold the first letter of the food group name and if multiple ones, it holds the first letter for each group it fits in. (i.e. milk would have D for Dairy, yogurt would have FM) Is this possible (assuming each item can only fit in one or two food groups)? Thanks! "JulieD" wrote: Hi Bo check out http://www.contextures.com/xlDataVal02.html -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bo" wrote in message ... I am currently working on a project that I can't figure out the last few details. This is similar to what I have: Program: (followed by validated list of programs as a dropdown) Features: (followed by validated list of features as a dropdown) Table in another tab: Program Feature Word Table of Contents Excel Pivot Table Both Save Both Print I would like to limit the Features validation to only display features that are in the program the user chose (i.e., If they chose Word as the program, it should only show Table of Contents, Save, and Print in the dropdown). Any ideas on this? |
#4
|
|||
|
|||
Sorry, I meant FD for Fruit and Dairy near the end, not FM.
"Bo" wrote: That has some great information that could work, however, if possible I want to go one or two steps further. The example showed fruit in two categories. My categories are combined. Using the example on the site, let me restate the question. Let's say I have a list of the food groups (i.e. meats, dairy, fruits/veggies, etc.) and specific foods for each one listed in one large column. For each item of food, a second column states what food group it is in. This is done in my data tab. Now I go to my main tab and have a validated cell to select what food group I want to look at. I want a second validated cell to only show the foods in the food group. Now let's say all the foods in the data cell are alphabetical, so it is not possible to select only the fruits and name the column Fruits. How would I validate the second cell to show only the food items from the selected group? And if possible, one step further would be to note that maybe a food item could be classified as two different groups. Just for this example, let's say yogurt is both dairy and fruit (having milk products and fruit). Recognizing this, I change the food group column to hold the first letter of the food group name and if multiple ones, it holds the first letter for each group it fits in. (i.e. milk would have D for Dairy, yogurt would have FM) Is this possible (assuming each item can only fit in one or two food groups)? Thanks! "JulieD" wrote: Hi Bo check out http://www.contextures.com/xlDataVal02.html -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bo" wrote in message ... I am currently working on a project that I can't figure out the last few details. This is similar to what I have: Program: (followed by validated list of programs as a dropdown) Features: (followed by validated list of features as a dropdown) Table in another tab: Program Feature Word Table of Contents Excel Pivot Table Both Save Both Print I would like to limit the Features validation to only display features that are in the program the user chose (i.e., If they chose Word as the program, it should only show Table of Contents, Save, and Print in the dropdown). Any ideas on this? |
#5
|
|||
|
|||
Hi Bo
i do not know how to achieve this without using VBA, i guess you could put a worksheet_change event in place that looks at what is chosen in the first data / validation box and creates and populates a second data validation box meeting your requirements. So, first question - are you happy with a code solution? if so, i will need to know what is the cell reference(s) of the first validation box what is the cell reference(s) of the second (dependent) box what do you want to do if a value has been selected in the second box and then someone changes their mind for the first box? also there will need to be a table somewhere that says "F" in the second column equals "Fruit" and "M" equals "Meat" - what is the reference of this table? if you don't want a code solution then i think what you're after is not possible. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bo" wrote in message ... That has some great information that could work, however, if possible I want to go one or two steps further. The example showed fruit in two categories. My categories are combined. Using the example on the site, let me restate the question. Let's say I have a list of the food groups (i.e. meats, dairy, fruits/veggies, etc.) and specific foods for each one listed in one large column. For each item of food, a second column states what food group it is in. This is done in my data tab. Now I go to my main tab and have a validated cell to select what food group I want to look at. I want a second validated cell to only show the foods in the food group. Now let's say all the foods in the data cell are alphabetical, so it is not possible to select only the fruits and name the column Fruits. How would I validate the second cell to show only the food items from the selected group? And if possible, one step further would be to note that maybe a food item could be classified as two different groups. Just for this example, let's say yogurt is both dairy and fruit (having milk products and fruit). Recognizing this, I change the food group column to hold the first letter of the food group name and if multiple ones, it holds the first letter for each group it fits in. (i.e. milk would have D for Dairy, yogurt would have FM) Is this possible (assuming each item can only fit in one or two food groups)? Thanks! "JulieD" wrote: Hi Bo check out http://www.contextures.com/xlDataVal02.html -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bo" wrote in message ... I am currently working on a project that I can't figure out the last few details. This is similar to what I have: Program: (followed by validated list of programs as a dropdown) Features: (followed by validated list of features as a dropdown) Table in another tab: Program Feature Word Table of Contents Excel Pivot Table Both Save Both Print I would like to limit the Features validation to only display features that are in the program the user chose (i.e., If they chose Word as the program, it should only show Table of Contents, Save, and Print in the dropdown). Any ideas on this? |
#6
|
|||
|
|||
Since it appears not possible without code, I suppose VBA would work, but I
am not familiar with the language. I am a beginner programmer in C++, however, so if you could include some code, I'm sure I could figure it out. To answer some questions you have, let's say I have a label in the first tab ("Report") with "Enter food group" in cell A3 and cell D3 has the dropdown with the list of food groups. For simplicity, let say there are three to choose from (Fruits, Meats, and Dairy). These are listed in Data!A2:A4 with a label of FoodGroups that is validated as a list in D3 for the dropdown. Next I have A5 with a label "Choose your food" with a dropdown in cell D5 with a validation for a list using Data!B8:B243 for the food items. Data!A8:A243 has the food group (values for these cells can be: Dairy, Meats, Fruits, Dairy/Meat, Dairy/Fruits, Meat/Fruits). If I choose in cell D3 "Fruits", I would like the dropdown in D5 to show food items that have the food group Fruits, Dairy/Fruits, and Meat/Fruits only. If they choose Fruits in the first cell and select Milk in the second, then change their mind in the first, the second cell should switch to a blank cell (or the first item of the list, whichever is easier). I would also like the ability to add additional items or delete others in the future without drastically changing the calculations. Thanks in advance! "JulieD" wrote: Hi Bo i do not know how to achieve this without using VBA, i guess you could put a worksheet_change event in place that looks at what is chosen in the first data / validation box and creates and populates a second data validation box meeting your requirements. So, first question - are you happy with a code solution? if so, i will need to know what is the cell reference(s) of the first validation box what is the cell reference(s) of the second (dependent) box what do you want to do if a value has been selected in the second box and then someone changes their mind for the first box? also there will need to be a table somewhere that says "F" in the second column equals "Fruit" and "M" equals "Meat" - what is the reference of this table? if you don't want a code solution then i think what you're after is not possible. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway |
#7
|
|||
|
|||
Hi Bo
i'll try to have a look at this in detail tomorrow and get back to you ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bo" wrote in message ... Since it appears not possible without code, I suppose VBA would work, but I am not familiar with the language. I am a beginner programmer in C++, however, so if you could include some code, I'm sure I could figure it out. To answer some questions you have, let's say I have a label in the first tab ("Report") with "Enter food group" in cell A3 and cell D3 has the dropdown with the list of food groups. For simplicity, let say there are three to choose from (Fruits, Meats, and Dairy). These are listed in Data!A2:A4 with a label of FoodGroups that is validated as a list in D3 for the dropdown. Next I have A5 with a label "Choose your food" with a dropdown in cell D5 with a validation for a list using Data!B8:B243 for the food items. Data!A8:A243 has the food group (values for these cells can be: Dairy, Meats, Fruits, Dairy/Meat, Dairy/Fruits, Meat/Fruits). If I choose in cell D3 "Fruits", I would like the dropdown in D5 to show food items that have the food group Fruits, Dairy/Fruits, and Meat/Fruits only. If they choose Fruits in the first cell and select Milk in the second, then change their mind in the first, the second cell should switch to a blank cell (or the first item of the list, whichever is easier). I would also like the ability to add additional items or delete others in the future without drastically changing the calculations. Thanks in advance! "JulieD" wrote: Hi Bo i do not know how to achieve this without using VBA, i guess you could put a worksheet_change event in place that looks at what is chosen in the first data / validation box and creates and populates a second data validation box meeting your requirements. So, first question - are you happy with a code solution? if so, i will need to know what is the cell reference(s) of the first validation box what is the cell reference(s) of the second (dependent) box what do you want to do if a value has been selected in the second box and then someone changes their mind for the first box? also there will need to be a table somewhere that says "F" in the second column equals "Fruit" and "M" equals "Meat" - what is the reference of this table? if you don't want a code solution then i think what you're after is not possible. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation, cell protection or other method? | Excel Discussion (Misc queries) | |||
DATA VALIDATION IN REVERSE #2 (FOR JULIE D.) | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Data Validation Window? | Excel Discussion (Misc queries) |