Dynamic arrays

I can create arrays but what about dynamic arrays?
E.g. Say I have an employee and an employee ID on one sheet (My employee
sheet) and then on another sheet I have ratings for the month for that
employer using data validation. How can I get the data validation to grow or
shrink as more or less employees are added?

Try this:

If your employee list is in Cells A1:B100 on the EmpList sheet:
•Go to the EmpList sheet
•Inser>Name>Define
-Name: LU_Emp
-Refers To: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),2)
-Click [OK]

That should create a range named LU_Emp that referes to cells A1:B100 on the
EmpList sheet. If you add a name to the bottom of the list, the new
reference will automatically change to cells A1:B101

•Set the cell validations to refer to the LU_Emp named range
-When in the Source field, click press the [F3] key to see the list of names
and select LU_Emp.

Does that help?

••••••••••
Regards,
Ron


"Driver" wrote:

> I can create arrays but what about dynamic arrays?
> E.g. Say I have an employee and an employee ID on one sheet (My employee
> sheet) and then on another sheet I have ratings for the month for that
> employer using data validation. How can I get the data validation to grow or
> shrink as more or less employees are added?

CORRECTION:
'Refers To' *should be*: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),1)

instead of Refers To: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),2)

Data Validation only allows 1 row or column in the referenced range. My prev
formula made a 2 column dynamic list.

••••••••••
Regards,
Ron


"Ron Coderre" wrote:

> Try this:
>
> If your employee list is in Cells A1:B100 on the EmpList sheet:
> •Go to the EmpList sheet
> •Inser>Name>Define
> -Name: LU_Emp
> -Refers To: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),2)
> -Click [OK]
>
> That should create a range named LU_Emp that referes to cells A1:B100 on the
> EmpList sheet. If you add a name to the bottom of the list, the new
> reference will automatically change to cells A1:B101
>
> •Set the cell validations to refer to the LU_Emp named range
> -When in the Source field, click press the [F3] key to see the list of names
> and select LU_Emp.
>
> Does that help?
>
> ••••••••••
> Regards,
> Ron
>
>
> "Driver" wrote:
>
> > I can create arrays but what about dynamic arrays?
> > E.g. Say I have an employee and an employee ID on one sheet (My employee
> > sheet) and then on another sheet I have ratings for the month for that
> > employer using data validation. How can I get the data validation to grow or
> > shrink as more or less employees are added?

Thanks Ron,

It took a bit of time but I finally got my head around it.

"Ron Coderre" wrote:

> CORRECTION:
> 'Refers To' *should be*: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),1)
>
> instead of Refers To: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),2)
>
> Data Validation only allows 1 row or column in the referenced range. My prev
> formula made a 2 column dynamic list.
>
> ••••••••••
> Regards,
> Ron
>
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > If your employee list is in Cells A1:B100 on the EmpList sheet:
> > •Go to the EmpList sheet
> > •Inser>Name>Define
> > -Name: LU_Emp
> > -Refers To: =OFFSET(EmpList!$A$1,,,COUNTA(EmpList!$A:$A),2)
> > -Click [OK]
> >
> > That should create a range named LU_Emp that referes to cells A1:B100 on the
> > EmpList sheet. If you add a name to the bottom of the list, the new
> > reference will automatically change to cells A1:B101
> >
> > •Set the cell validations to refer to the LU_Emp named range
> > -When in the Source field, click press the [F3] key to see the list of names
> > and select LU_Emp.
> >
> > Does that help?
> >
> > ••••••••••
> > Regards,
> > Ron
> >
> >
> > "Driver" wrote:
> >
> > > I can create arrays but what about dynamic arrays?
> > > E.g. Say I have an employee and an employee ID on one sheet (My employee
> > > sheet) and then on another sheet I have ratings for the month for that
> > > employer using data validation. How can I get the data validation to grow or
> > > shrink as more or less employees are added?