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