Posted by Maureen Hill on November 15, 2001 10:58 AM
I have a drop down box in Column 1 of a spreadsheet. The box is populated from a list with a maximum number of list items of 27 (all text). I have a drop down box in Column 2. I want to be able to populate the drop down box from 27 different lists (related directly to the 27 items in the drop down box for Column 1). Each of these 27 different lists has a maximum number of list items of 10. The only way I know of to do this would be to enter a formula having 27 different IF statements so that the drop down box in Column 2 is based on what is chosen from the drop down box in Column 1. I need to know if there is an easier and less cumbersome way to do this and, if not, if there is a maximum number of IF statements that can be put in one formula.
Assuming I can make this work, my second challenge is ...
I need to automatically populate Columns 3 and 4 with data from another worksheet within the same workbook. The data is directly related to what is chosen in Column 2. Specifically, the value chosen in Column 2 is a description of various project components (with the potential number of these being 270!) and the values in Columns 3 and 4 are budget code information that is entered in the other worksheet. So ... when the operator selects the Project Phase from the drop down box in Column 2, I want Columns 3 and 4 to be valued automatically using the budget code information from two different columns in another worksheet within the same workbook. Again, the only way I know of to do this is using IF statements ... I would have to enter 270 different IF statements in each field in Columns 3 and 4 to make this work. WAY too cumbersome ... and I'm sure there's a better way.
PLEASE HELP !!!