Excel 2007 Dynamic Dependent Data Validation Lists issues
OK, I have a spreadsheet that's is intended to be used as an estimating sheet for painting contractors. I have Sheet 1, that is going to have dynamic dependent lists across the whole row for the initial parent choice in the first cell of the row. On Sheet 2, I have a (mock) CSV file that's going to be imported into Excel through our system (we'll worry about that later, not part of this issue). On Sheet 3, I have some formulas that are "running in the background" - meaning, it's a sheet just for putting any formulas on so we can hide that sheet.
Right now, on the CSV, I have a material, let's call it Mat1, that has either a Y or an N for three different properties, those being Wood, Metal, and Concrete, in that order. On Sheet 1, I have a Named Range for all the materials, and on Sheet 3, the formulas are indexing the Wood, Metal, and Concrete (separately) columns, and matching that to the chosen material from the Data Validation list on Sheet 1. Then it takes those Ys and Ns, and changes them to correspond to either of the three types. Then I have another Named Range on those three cells so I can make a list of available options for the chosen material. So if I choose Mat1, the next list will show only Wood available. If I were to choose Mat7, all three would be available
My problem is, this method only works for the first row of Sheet 1, and I need it to be used for as many rows down as the painter needs to put down. So if there's 14 things that need to be estimated, there needs to be 14 rows. Only 8 things, then only 8 rows. I'm thinking something like a button to add new rows would be the way to do this, but right now I want to focus on getting the lists to work on x rows.
I've included my sample file so everyone can take a look: Estimating-test.xls
This also seems like a not-so-pretty way to do this, so I am open to any better ideas out there. I don't know Excel all that well, and don't know VBA at all, and have just been mashing things together as I go along. I'm actually kind of impressed that I've gotten myself this far.
This might sound complicated and convoluted, so if anything is unclear, please ask. This makes sense to me, but I've been in the middle of it for this week so far.
Thanks in advance
Right now, on the CSV, I have a material, let's call it Mat1, that has either a Y or an N for three different properties, those being Wood, Metal, and Concrete, in that order. On Sheet 1, I have a Named Range for all the materials, and on Sheet 3, the formulas are indexing the Wood, Metal, and Concrete (separately) columns, and matching that to the chosen material from the Data Validation list on Sheet 1. Then it takes those Ys and Ns, and changes them to correspond to either of the three types. Then I have another Named Range on those three cells so I can make a list of available options for the chosen material. So if I choose Mat1, the next list will show only Wood available. If I were to choose Mat7, all three would be available
My problem is, this method only works for the first row of Sheet 1, and I need it to be used for as many rows down as the painter needs to put down. So if there's 14 things that need to be estimated, there needs to be 14 rows. Only 8 things, then only 8 rows. I'm thinking something like a button to add new rows would be the way to do this, but right now I want to focus on getting the lists to work on x rows.
I've included my sample file so everyone can take a look: Estimating-test.xls
This also seems like a not-so-pretty way to do this, so I am open to any better ideas out there. I don't know Excel all that well, and don't know VBA at all, and have just been mashing things together as I go along. I'm actually kind of impressed that I've gotten myself this far.
This might sound complicated and convoluted, so if anything is unclear, please ask. This makes sense to me, but I've been in the middle of it for this week so far.
Thanks in advance
ASKER
Not quite. I'll try and clarify.
The parent list (column A) is fine, it's the dependent list (column B) that I need help with. If I only have a single row, the whole thing works fine, but I need to be able to expand this down a few rows and still have the same functionality. The dependent list needs to be specific to the choice made in the parent list of that row. So if I choose "Mat3" from the list in A2, the list in B2 will reflect the available options based on the choice in A2.
Now, I run into problems when I try and expand this down to more rows, I am able to retain the functionality of column A since all that is is a Named Range, but what I lose is the functionality for the B column. I need a way to iterate the B column down multiple rows. Right now, the way the sheet works is on Sheet 3, B2 is referencing Sheet1!A2, and in C2:C4, there are formulas that are checking against B2 and adding the appropriate choices to those cells. That same C2:C4 is currently in a Named Range, and that's how the dependent list is filled in on Sheet 1. Because of that reference to Sheet1!A2, it makes the whole thing unable to used on multiple rows, and that's where I'm stuck.
I need a way to be able to add as many rows as needed and maintain the functionality of the dependent list specific to the row it's on.
The parent list (column A) is fine, it's the dependent list (column B) that I need help with. If I only have a single row, the whole thing works fine, but I need to be able to expand this down a few rows and still have the same functionality. The dependent list needs to be specific to the choice made in the parent list of that row. So if I choose "Mat3" from the list in A2, the list in B2 will reflect the available options based on the choice in A2.
Now, I run into problems when I try and expand this down to more rows, I am able to retain the functionality of column A since all that is is a Named Range, but what I lose is the functionality for the B column. I need a way to iterate the B column down multiple rows. Right now, the way the sheet works is on Sheet 3, B2 is referencing Sheet1!A2, and in C2:C4, there are formulas that are checking against B2 and adding the appropriate choices to those cells. That same C2:C4 is currently in a Named Range, and that's how the dependent list is filled in on Sheet 1. Because of that reference to Sheet1!A2, it makes the whole thing unable to used on multiple rows, and that's where I'm stuck.
I need a way to be able to add as many rows as needed and maintain the functionality of the dependent list specific to the row it's on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I hope I got You right.
See the solution in the file.
Estimating-test.xls