Patrick020
New Member
- Joined
- Mar 20, 2018
- Messages
- 15
Hi everyone
I have a table in A1:D9 in which a unit is plotted for each person against various locations.
Cell G1 is a data validation/drop down in which you can select a person from the table (in the example below I have taken Person 2). The formula therefore needs to reference cell G1.
I would like a formula to drag down from G2 which lists the locations for that person from the highest ranked to the lowest ranked. In the example below New York has a 12 for Person 2 which is the highest value – hence NewYork is listed first.
If possible I would also like a formula to drag down from F2which pulls the unit associated with that person and location – so in the example below F2 is 12 as Person 2 plots 12 for New York.
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"] A<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] B<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] C<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] D<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"] E<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"] F<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] G<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] City<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] Person 1 <o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] Person 2<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] Person 3<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] Unit <o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Person 2<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Berlin <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 6<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 12<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] New York<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Los Angeles<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Paris <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] London <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 8<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Los Angeles <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Madrid <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Madrid<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Moscow <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 2<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] New York <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 5<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] 12<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 8<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Paris <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 9<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Rome<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 11<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 10<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
</tbody>[/TABLE]
Hopefully that is fairly clear but let me know if not!
Thanks
Patrick
I have a table in A1:D9 in which a unit is plotted for each person against various locations.
Cell G1 is a data validation/drop down in which you can select a person from the table (in the example below I have taken Person 2). The formula therefore needs to reference cell G1.
I would like a formula to drag down from G2 which lists the locations for that person from the highest ranked to the lowest ranked. In the example below New York has a 12 for Person 2 which is the highest value – hence NewYork is listed first.
If possible I would also like a formula to drag down from F2which pulls the unit associated with that person and location – so in the example below F2 is 12 as Person 2 plots 12 for New York.
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 104, bgcolor: transparent"] A<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] B<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] C<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] D<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"] E<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"] F<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] G<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] City<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] Person 1 <o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] Person 2<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] Person 3<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] Unit <o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Person 2<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 2<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Berlin <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 6<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 12<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] New York<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Los Angeles<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Paris <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 4<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] London <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 8<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 3<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Los Angeles <o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 5<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Madrid <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] 1<o></o>
[/TD]
[TD="width: 98, bgcolor: transparent"] Madrid<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 6<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Moscow <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 2<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] New York <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"] 5<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"] 12<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 8<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Paris <o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 7<o></o>
[/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 9<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"] Rome<o></o>
[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"] 11<o></o>
[/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] 10<o></o>
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"] <o> </o>
[/TD]
[TD="width: 98, bgcolor: transparent"] <o> </o>
[/TD]
[/TR]
</tbody>[/TABLE]
Hopefully that is fairly clear but let me know if not!
Thanks
Patrick
Last edited by a moderator: