Dynamic Table

[zinah]

Hi,

I have table that is dynamic and changes whenever I change the a value (Employee ID) in specific cell of a drop-down list. So when I change this ID then locations related to this ID should change, the issue I'm facing is that each Employee ID has different number of locations, some has 5, others have 20+ and so on. How can I write a formula that bring all locations according to the Employee ID?

The current formula that I'm using right now are below and I need to modify them:

the first line formula is:

=IFERROR(INDEX('Dropdown List'!$U$2:$U$99,MATCH(Org_Layer!$AC$10,'Dropdown List'!$T$2:$T$99,0)),"")

then I add 1 till I reach to 25 which is not a great option:

=IFERROR(INDEX('Dropdown List'!$U$2:$U$99,MATCH(Org_Layer!$AC$10,'Dropdown List'!$T$2:$T$99,0)+1),"")

=IFERROR(INDEX('Dropdown List'!$U$2:$U$99,MATCH(Org_Layer!$AC$10,'Dropdown List'!$T$2:$T$99,0)+2),"")

 

[ClaireS]

It would be useful to know where you are putting this list! Instead of using +1, +2 etc, use the ROWS function, referencing the top cell as a fixed reference and "this cell" as a relative one. If we assume that the list you want to populate starts in C2, then this part of the formula should read +ROWS($C$2:C2)-1. For the first row, this would count the number of rows in the range C2:C2 and get 1, then subtract 1 to give 0, so you are not making any change. After that, it will count 2 rows, subtract 1 to give +1, 3 rows gives +2 and so on down.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Locations,SMALL(IF(EmpIDs=id,ROW(Locations)-ROW(INDEX(Locations,1,1))+1),ROWS($1:1))),"")

 

[zinah]

Hi @ClaireS, thank you for your help. Here's the answer to your question:
The list is in a separate tab which is "Dropdown list", and the ID column is T2:T99 and locations column is U2:U99.
and the formula that needs to populate all the location in tab name "Org_Layer" and it starts from cell AC19:AC44.

 

[zinah]

Hi @Aladin Akyurek
Thank you for your help, when I tried your formula, the result gave me blank result, and below is the formula I used after I updated your formula with cells and references of my data. Is there something I did wrong?
Locations = VPO_ID_Loc or Dropdown List'!$U$2:$U$99
EmpIDs = VPO_ID or Dropdown List'!$T$2:$T$99
And this is the formula after the update:
The start cell where I need to populate my first location is AC19

{=IFERROR(INDEX(VPO_ID_Loc,SMALL(IF(VPO_ID=AC10,ROW(VPO_ID_Loc)-ROW(INDEX(VPO_ID_Loc,1,1))+1),ROWS($AP19:AP19))),"")}

 

[ClaireS]

Try this in AC19, then copy down:

[LEFT][COLOR=#333333][FONT=monospace]=IFERROR(INDEX('Dropdown List'!$U$2:$U$99,MATCH(Org_Layer!$AC$10,'Dropdown List'!$T$2:$T$99,0)[/FONT][/COLOR][COLOR=#333333][FONT=monospace][COLOR=#574123][FONT=Tahoma]+ROWS($AC$19:AC19)-1[/FONT][/COLOR]),"")[/FONT][/COLOR][/LEFT]

Let me know if this works.

Good luck!

 

[zinah]

Awesome! It worked perfectly great! Thank you so much ClaireS
Have a great one!

 

[zinah]

Aladin Akyurek
Just want to update you, I have updated my data and table and your suggested formula worked perfectly great, thank you so much for your help

{=IFERROR(INDEX(VPO_Loc,SMALL(IF(VPO_ID_Loc=$AC$10,ROW(VPO_Loc)-ROW(INDEX(VPO_Loc,1,1))+1),ROWS($1:1))),"")}

 

[Aladin Akyurek]

You are welcome.