COMPLEX EXCEL SUB TABLE HELP
Here's another CHALLENGE:
I am trying to extract data from a separate table in a separate worksheet to feed another table in another worksheet. I have attached the file so you can see what I am working with. Currently in the RED tab I have attempted one formula; however, this is not what I am looking for, completely. I like the formula; however, I would like to only update this table with individuals whose work status is "Active" as listed in the YELLOW tab. this is the formula that I tried; however, it did not work....
=IF(INDEX(WorkStatus,MATCH(Payroll[Position]&Payroll[Divison]&Payroll[Location],[Position]&[Divison]&[Location],0))="Active",INDEX([Address 1],MATCH(1,([Last Name]=$A35)*([First Name]=$B35)*([Position]=$AH35)*([Divison]=$AI35)*([Location]=$AJ35),0)),="Inactive File")
As I updated the table in the YELLOW tab, I would like the table in the RED tab to be updated for all individuals that are active...
Please help
| Attachment | Size |
|---|---|
| SAMPLE_(091512).xlsx | 96.87 KB |
RE: Complex Excel Sub Table Help
Hi,
Here's, this is your formula with some modifications:
= IF(INDEX(WorkStatus, MATCH(1, (EmployeeDetail[Last Name] = $A17) * (EmployeeDetail[First Name] = $B17) *
(EmployeeDetail[Position] = $AH17) * (EmployeeDetail[Divison] = $AI17) * (EmployeeDetail[Location] = $AJ17), 0)) = "Active",
INDEX(EmployeeDetail[Address 1], MATCH(1, (EmployeeDetail[Last Name] = $A17) * (EmployeeDetail[First Name] = $B17) *
(EmployeeDetail[Position] = $AH17) * (EmployeeDetail[Divison] = $AI17) * (EmployeeDetail[Location] = $AJ17), 0)), "Inactive File")
I replaced the first MATCH with the second one from your formula and remove the equal sign before "Inactive file".
For the empty rows in your table you will get #N/A error. If you want to avoid this you can wrap your formula in the IFERROR one. For example:
= IFERROR(Your_Formula, "")
P.S. Just for clarity and for the others that will read this topic: this is an array formula, and after you type it, must be press CTRL + SHIFT + ENTER instead of only ENTER.
Best regards.