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.

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.

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

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
>
>

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

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, Data>Valadation>List>Refer 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
>
>

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, Data>Valadation>List>Refer 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

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, Data>Valadation>List>Refer 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
>
>

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, Data>Valadation>List>Refer 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