Returning row and column headers

  • Thread starter Thread starter Lindleman
  • Start date Start date
L

Lindleman

I have a spreadsheet that contains soccer player positions for our
team. I have the positions layed out in a range that has the field
positions as column headers and the period played as the row headers.
The names of all the players are filled in for each of the four
periods and for each position. I would like to be able to extract from
this range, each boys position and period by using a formula. i.e.
Jake is playing center forward in the second period. I would like to
have a seperate range that has the players name and all 4 periods
listed and be able to ectract from the data range, the position he
will be playing for each period. I know there is probably a simple way
to do this, but I cannot get it right.

Period Left Forward Right Forward Right Midfielder
1 Jake Billy Trevor
2 Drew Austin Jake
3 Drew Billy Austin
4 Jake Austin Trevor


Trevor
1 ???
2 ???
3 ???
4 ???

Thanks
 
There are a myriad ways of doing this. Here is one way -
see what you think. If it is not what you want write back
and I'll try and improve.

Assume your data is set as follows (i.e. Starting with
the table you posted in cell A1

A B C D
1 Period Left Forward Right Forward Right Midfielder
2 1 Jake Billy Trevor
3 2 Drew Austin Jake
4 3 Drew Billy Austin
5 4 Jake Austin Trevor
6
7 Period Trevor
8 1
9 2
10 3
11 4

Now, in cell B8 (i.e. directly below 'Trevor') type in
the following formula (it is easier to cut and paste
it...)

=IF(MATCH($B$7,B2:D2,0)=1,$B$1,IF(MATCH($B$7,B2:D2,0)
=2,$C$1,IF(MATCH($B$7,B2:D2,0)=3,$D$1)))
Now click and drag down the formula to cell B11.

This ought to work. If a player is not playing in a
period then the cell returns #N/A. If you type a new name
of a player in cell B7 then it will update the positions
you need.

Try this out. I hope it is clear. I will check back so if
there are issues make a new post and I will see it.

Regards


Alex
 
Thanks for the quick response Alex!
Your formula works well. Unfortunately, I can't use it because I have
11 positions and Excel will only allow nesting of 7 functions.
Therefor, I get an error once I get past the 7th "IF" Statement. I
should have been more specific to show how many positions I had. Any
other Ideas?

Thanks again,

Lindy
 
Lindy

Ok. Here we go.

Assume the same set up as before starting in cell A1. Now
you have 11 positions instead of four. So your data table
goes from A1 to L5

In cell B8 (where we placed the formual last time) type
this new formula in...

=OFFSET($B$7,-6,(MATCH($B$7,B2:L2,0)-1))

That works for all 11 positions.

Let me know if this is what you want. If not I shall get
my pencil and paper out once again!

No problem

Alex
 
I think I have this solved.

I used th following formula in the cells that I need to return the positions

=INDEX(A1:D5, 1, MATCH($B$7,$A$2:$D$2,0))

I just change the A2:D2 to A3:D3 to search in the next row.

I think this works fine and is much simpler. Hope this helps someone else.

L. Long
 
Back
Top