Dynamic arrays

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

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

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?

 

[Guest]

Thanks Ron,

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

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

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

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?