Hi. I'm trying to write up an estimating program in excel 2010 which requires some conditional drop down lists which I don't know how to make.
What I've done so far is make two sheets. On sheet2 I've added the lists from which my formulas on Sheet1 pull from. Sheet one now has a drop down list in Cell A1 from which you can select, say, "Sheet". Then, in cell A2 I've used Data Validation =Indirect($A$1) so that it gives me a drop down list of everything in my list of sheets. Now, say we select the sheet called "Foamcore" - so cell A2 now says FoamCore. This is as far as I've managed to get.
What I want to do now is make it so that when cell A2 is filled in, A3 automatically returns the price of FoamCore, A4 automatically returns the width, and A5 automatically returns the length. The way I have Sheet two with the data set up is as follows:
A1 = Sheet
B1 = Foamcore
C1 = Price
D1 = Width
E1 = Length
I've actually gone ahead and named each price in the price column after the corresponding material so that I can once again use the indirect function to make the prices appear on a drop down. But I can't move past this point because if I named my widths and lengths after the prices then when I update my prices the indirect functions would be invalid. So I need another tool that is NOT indirect unless I can get around this glitch. I know it's confusing, but any ideas?
What I've done so far is make two sheets. On sheet2 I've added the lists from which my formulas on Sheet1 pull from. Sheet one now has a drop down list in Cell A1 from which you can select, say, "Sheet". Then, in cell A2 I've used Data Validation =Indirect($A$1) so that it gives me a drop down list of everything in my list of sheets. Now, say we select the sheet called "Foamcore" - so cell A2 now says FoamCore. This is as far as I've managed to get.
What I want to do now is make it so that when cell A2 is filled in, A3 automatically returns the price of FoamCore, A4 automatically returns the width, and A5 automatically returns the length. The way I have Sheet two with the data set up is as follows:
A1 = Sheet
B1 = Foamcore
C1 = Price
D1 = Width
E1 = Length
I've actually gone ahead and named each price in the price column after the corresponding material so that I can once again use the indirect function to make the prices appear on a drop down. But I can't move past this point because if I named my widths and lengths after the prices then when I update my prices the indirect functions would be invalid. So I need another tool that is NOT indirect unless I can get around this glitch. I know it's confusing, but any ideas?
