Formula Questions
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 !!!
Posted by Aladin Akyurek on November 15, 2001 11:23 AM
Maureen,
I'd suggest using a separate sheet for all your lists.
I'll assume a list of 27 text-items in a sheet called Lists.
Select all of the cells of this first list excepting the label that you might have. Go to the Name Box on the Formula Bar, type MLIST (from Main List), and enter.
Now create a sublist in Lists that is associated with you first item in MLIST. Select all of the cells of this new list, go the Name Box, type the name the first item in MLIST. Repeat this proc of creating sublists. The main thing each sublist is named with the name of the text-item with which it is associated in MLIST.
Now, go to the cell in the worksheet of interest where you want your first dropdown list.
Activate Data|Validation.
Select List for Allow.
Enter as source =MLIST.
Click OK.
Suppose that the cell where you created the dropdown list is A1.
Activate B1.
Activate Data|Validation.
Select List for Allow.
Enter as source =INDIRECT(A1).
Click OK.
Regarding your second query, you can use VLOOKUP to retrieve data from a table in a different worksheet. Go to that worksheet. Select all of the cells of the range where you have project related info including budget code information (don't select the labels). Go to the Name Box, type PDATA (from Project Data), and enter.
In the worksheet of dropdown lists,
in C1 enter: =VLOOKUP(B1,PDATA,the-column-number-from-which-you-want-puul-of-data,0)
Similarly, enter a VLOOKUP formula in D1 to retrieve another piece of data from PDATA.
If you don't know how VLOOKUP works, let us know.
Aladin
Posted by Maureen Hill on November 15, 2001 11:35 AM
Thank you SO much!
I'll try to follow your instructions and let you know how I make out.
I really appreciate your help!!! I was going cross-eyed trying to figure out a solution to these problems using my handy "Running Microsoft Excel 2000" manual. Definitely not bedtime reading.
Posted by Maureen Hill on November 15, 2001 12:25 PM
As I work through your instructions ... which are VERY helpful by the way ... it is evident that I neglected to mention something that appears to be relevant. The names in the MLIST are placed in that list by using the Paste Special / Paste Link command. They are originally designed to be entered by an operator creating the original project design on another worksheet, which then feeds the list(s). In the extremely likely event that the Project Names and/or Project Phases (columns 1 and 2, respecively) change, and I have linked the sublists to text value of the originally entered names, the drop down list in column 2 won't work (since the name it was related to no longer exists and has been replaced). I tried to enter the address of the name, rather than the text of the name (given its potentially changing nature), but I was given an Invalid Name error message. Is there any way around this so I can use the address of the value rather than the text value?
MANY THANKS, in advance, for your help !!!
