Dependent Validation List - Auto Populate

**himey77** · 02-24-2011, 11:47 PM

Please see the attached workbook:

How do I get the Supervisor name to auto-populate after selecting the department and employee?

**pike** · 02-25-2011, 02:28 AM

Hi himey77
you can combile index and match

Please Login or Register  to view this content.

fill down

**himey77** · 02-25-2011, 11:55 AM

Originally Posted by pike

Hi himey77
you can combile index and match

Please Login or Register  to view this content.

fill down

That worked, thank you!!!

Can someone please breakdown the formula so I can understand what it actually does?

**pike** · 02-25-2011, 06:45 PM

Hi himey77,
The formula may be easier to understand without the iF

Please Login or Register  to view this content.

The match returns the item/row number in the column/array
so we ask your match (d2) to look for the name in the column of names (EmployeeColumn) and return its row number

The index use in the row column numbers to return a cell value

so with the row number from the match of the name we just look at the next column (ValidationLists!C:C) with the supers name and return that value

the if statment is use to stop NA errors if no name is found

**himey77** · 02-25-2011, 11:07 PM

Originally Posted by pike

Hi himey77,
The formula may be easier to understand without the iF

Please Login or Register  to view this content.

The match returns the item/row number in the column/array
so we ask your match (d2) to look for the name in the column of names (EmployeeColumn) and return its row number

The index use in the row column numbers to return a cell value

so with the row number from the match of the name we just look at the next column (ValidationLists!C:C) with the supers name and return that value

the if statment is use to stop NA errors if no name is found

Thanks for your help, Pike. The explanation is much appreciated!

"Teach a man to fish...."