| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
data validation - Drop Down Lists
I have set up a drop down in my first column lsiting 10 company departments.
In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. |
|
#2
|
|||
|
|||
|
create a single column look up table for the company departments, and a
double column table for employees, showing the department in the first column and the employees names in the second giving each table a name. To select the department; using data validation, select from allow 'list' and in the source type =department_list_name. For your second look up, again select list and in the source, type =(INDIRECT(VLOOKUP(x,employyee_list_name,2,FALSE)) where x is the cell reference of the result of the department lookup. This will then only give the employees applicable to the selected department. The employee list can be changed whenever the need arises. Hope this helps. "Annie" wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. |
|
#3
|
|||
|
|||
|
Did you try the instructions for dynamic lists?
http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#4
|
|||
|
|||
|
Yes, I have followed the infor for Dynamic Lists. I have my first drop down
for Departments (A1:A10) and I have named several columns (F,J,H,I) with employees listed. I do not think I am entering the formula correctly. "Debra Dalgleish" wrote: Did you try the instructions for dynamic lists? http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
|
|||
|
|||
|
The first cell in each column of employee names should be named for the
department. For example, cell F1 might be named "Accounting" Each column of employee names should be named Dept&Col. For example, column F would be AccountingCol Then, in the dependent validation, you refer to the cell with the department name (cell E2 in this example): =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT($E2&"Col ")),1) If your deparment names contain spaces, or some other characters, you'd have to adjust the names and formulas. Annie wrote: Yes, I have followed the infor for Dynamic Lists. I have my first drop down for Departments (A1:A10) and I have named several columns (F,J,H,I) with employees listed. I do not think I am entering the formula correctly. "Debra Dalgleish" wrote: Did you try the instructions for dynamic lists? http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#6
|
|||
|
|||
|
Thank You for the help. I am still doing something wrong.
Column A has the drop-down for my departments. I entered employee names in columns F, G, H. I named the first line of each column the same as their department in the drop down list and I named the columns Accountingcol, Transportationcol, Purchasingcol. Column B is where I want to see the drop down for my employee lists. (highlight column B, DataValadationListRefer to: =OFFSET(INDIRECT( AT THIS POINT I AM NOT CERTAIN IF I SHOULD CLICK ON COLUMN A OR CLICK ON CELL A-1 OF THE DEPARTMENT DROP DOWN LIST? =OFFSET(INDIRECT(???),0,0,COUNTA(INDIRECT( AT THIS POINT, I AM NOT CERTAIN WHICH FIELD TO CLICK? &"COL" I AM NOT CERTAIN WHAT TO ENTER AT THIS POINT. (NOTHING I ENTER IS CORRECT) THANKS AGAIN FOR YOUR HELP! "Debra Dalgleish" wrote: The first cell in each column of employee names should be named for the department. For example, cell F1 might be named "Accounting" Each column of employee names should be named Dept&Col. For example, column F would be AccountingCol Then, in the dependent validation, you refer to the cell with the department name (cell E2 in this example): =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT($E2&"Col ")),1) If your deparment names contain spaces, or some other characters, you'd have to adjust the names and formulas. Annie wrote: Yes, I have followed the infor for Dynamic Lists. I have my first drop down for Departments (A1:A10) and I have named several columns (F,J,H,I) with employees listed. I do not think I am entering the formula correctly. "Debra Dalgleish" wrote: Did you try the instructions for dynamic lists? http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#7
|
|||
|
|||
|
In both places in your formula, you should refer to cell A1, which
contains the department name. Annie wrote: Thank You for the help. I am still doing something wrong. Column A has the drop-down for my departments. I entered employee names in columns F, G, H. I named the first line of each column the same as their department in the drop down list and I named the columns Accountingcol, Transportationcol, Purchasingcol. Column B is where I want to see the drop down for my employee lists. (highlight column B, DataValadationListRefer to: =OFFSET(INDIRECT( AT THIS POINT I AM NOT CERTAIN IF I SHOULD CLICK ON COLUMN A OR CLICK ON CELL A-1 OF THE DEPARTMENT DROP DOWN LIST? =OFFSET(INDIRECT(???),0,0,COUNTA(INDIRECT( AT THIS POINT, I AM NOT CERTAIN WHICH FIELD TO CLICK? &"COL" I AM NOT CERTAIN WHAT TO ENTER AT THIS POINT. (NOTHING I ENTER IS CORRECT) THANKS AGAIN FOR YOUR HELP! "Debra Dalgleish" wrote: The first cell in each column of employee names should be named for the department. For example, cell F1 might be named "Accounting" Each column of employee names should be named Dept&Col. For example, column F would be AccountingCol Then, in the dependent validation, you refer to the cell with the department name (cell E2 in this example): =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT($E2&"Col ")),1) If your deparment names contain spaces, or some other characters, you'd have to adjust the names and formulas. Annie wrote: Yes, I have followed the infor for Dynamic Lists. I have my first drop down for Departments (A1:A10) and I have named several columns (F,J,H,I) with employees listed. I do not think I am entering the formula correctly. "Debra Dalgleish" wrote: Did you try the instructions for dynamic lists? http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#8
|
|||
|
|||
|
Thank you for being so patient and helpful! My formula is still not correct -
I think iI may be the "Col" info I am entering. rong. Column A - Drop Down allowing you to select the Department Name (A1 would be the cell to use in the formula) Column F - Named AccountingCol (F1 Accounting) Column G - Named PurchasingCol (G1 Purchasing) Column H - Named TransportaionCol (H1 Transportation) =OFFSEET(INDIRECT(A1),0,0,COUNTA(INDIRECT(A1&????? Am I entering this correctly to this point? What do I enter to indicate the Columns? Again, Thank You for being so helpful & patient! Annie "Debra Dalgleish" wrote: In both places in your formula, you should refer to cell A1, which contains the department name. Annie wrote: Thank You for the help. I am still doing something wrong. Column A has the drop-down for my departments. I entered employee names in columns F, G, H. I named the first line of each column the same as their department in the drop down list and I named the columns Accountingcol, Transportationcol, Purchasingcol. Column B is where I want to see the drop down for my employee lists. (highlight column B, DataValadationListRefer to: =OFFSET(INDIRECT( AT THIS POINT I AM NOT CERTAIN IF I SHOULD CLICK ON COLUMN A OR CLICK ON CELL A-1 OF THE DEPARTMENT DROP DOWN LIST? =OFFSET(INDIRECT(???),0,0,COUNTA(INDIRECT( AT THIS POINT, I AM NOT CERTAIN WHICH FIELD TO CLICK? &"COL" I AM NOT CERTAIN WHAT TO ENTER AT THIS POINT. (NOTHING I ENTER IS CORRECT) THANKS AGAIN FOR YOUR HELP! "Debra Dalgleish" wrote: The first cell in each column of employee names should be named for the department. For example, cell F1 might be named "Accounting" Each column of employee names should be named Dept&Col. For example, column F would be AccountingCol Then, in the dependent validation, you refer to the cell with the department name (cell E2 in this example): =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT($E2&"Col ")),1) If your deparment names contain spaces, or some other characters, you'd have to adjust the names and formulas. Annie wrote: Yes, I have followed the infor for Dynamic Lists. I have my first drop down for Departments (A1:A10) and I have named several columns (F,J,H,I) with employees listed. I do not think I am entering the formula correctly. "Debra Dalgleish" wrote: Did you try the instructions for dynamic lists? http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
|
|||
|
|||
|
You're welcome, and thanks for providing more information. Based on what
you've described, the formula would be: =OFFSET(INDIRECT($A1),0,0,COUNTA(INDIRECT($A1&"Col ")),1) If you select Accounting from the dropdown in cell A1, the formula above would be interpreted as: =OFFSET(Accounting,0,0,COUNTA(AccountingCol),1) Annie wrote: Thank you for being so patient and helpful! My formula is still not correct - I think iI may be the "Col" info I am entering. rong. Column A - Drop Down allowing you to select the Department Name (A1 would be the cell to use in the formula) Column F - Named AccountingCol (F1 Accounting) Column G - Named PurchasingCol (G1 Purchasing) Column H - Named TransportaionCol (H1 Transportation) =OFFSEET(INDIRECT(A1),0,0,COUNTA(INDIRECT(A1&????? Am I entering this correctly to this point? What do I enter to indicate the Columns? Again, Thank You for being so helpful & patient! Annie "Debra Dalgleish" wrote: In both places in your formula, you should refer to cell A1, which contains the department name. Annie wrote: Thank You for the help. I am still doing something wrong. Column A has the drop-down for my departments. I entered employee names in columns F, G, H. I named the first line of each column the same as their department in the drop down list and I named the columns Accountingcol, Transportationcol, Purchasingcol. Column B is where I want to see the drop down for my employee lists. (highlight column B, DataValadationListRefer to: =OFFSET(INDIRECT( AT THIS POINT I AM NOT CERTAIN IF I SHOULD CLICK ON COLUMN A OR CLICK ON CELL A-1 OF THE DEPARTMENT DROP DOWN LIST? =OFFSET(INDIRECT(???),0,0,COUNTA(INDIRECT( AT THIS POINT, I AM NOT CERTAIN WHICH FIELD TO CLICK? &"COL" I AM NOT CERTAIN WHAT TO ENTER AT THIS POINT. (NOTHING I ENTER IS CORRECT) THANKS AGAIN FOR YOUR HELP! "Debra Dalgleish" wrote: The first cell in each column of employee names should be named for the department. For example, cell F1 might be named "Accounting" Each column of employee names should be named Dept&Col. For example, column F would be AccountingCol Then, in the dependent validation, you refer to the cell with the department name (cell E2 in this example): =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT($E2&"Col ")),1) If your deparment names contain spaces, or some other characters, you'd have to adjust the names and formulas. Annie wrote: Yes, I have followed the infor for Dynamic Lists. I have my first drop down for Departments (A1:A10) and I have named several columns (F,J,H,I) with employees listed. I do not think I am entering the formula correctly. "Debra Dalgleish" wrote: Did you try the instructions for dynamic lists? http://www.contextures.com/xlDataVal02.html#Dynamic If so, where are you getting confused? Annie wrote: I have set up a drop down in my first column lsiting 10 company departments. In the second column I would like a drop down showing the employees for the specific department selected in the first column. I have looked at the info on the Contextures site, and I am certain I need to do a Dynamic list since employees can change, but I I am still confused. I would appreciate any help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing data validation scenarios | Excel Worksheet Functions | |||
| 6 Data Validation lists depending on 1 cell value | Excel Discussion (Misc queries) | |||
| Error Message with Data Validation | Excel Discussion (Misc queries) | |||
| Data Validation - Create dependent lists | Excel Discussion (Misc queries) | |||
| how do I add data validation dropdown lists to a Form | Excel Worksheet Functions | |||
Linear Mode
