#### Returning row and column headers

```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
```
 0
llong6590 (3)
9/15/2004 3:17:28 AM
excel.misc 78881 articles. 5 followers.

4 Replies
449 Views

Similar Articles

[PageSpeed] 10

```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

>-----Original Message-----
>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
>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
>.
>
```
 0
anonymous (74722)
9/15/2004 8:57:27 AM
```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

"Alex" <anonymous@discussions.microsoft.com> wrote in message news:<133d01c49b02\$066811e0\$a601280a@phx.gbl>...
> 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
>
> >-----Original Message-----
> >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
> >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
> >.
> >
```
 0
llong6590 (3)
9/15/2004 6:04:24 PM
```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
>-----Original Message-----
>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
>
>
>
>"Alex" <anonymous@discussions.microsoft.com> wrote in
message news:<133d01c49b02\$066811e0\$a601280a@phx.gbl>...
>> 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
>>
>> >-----Original Message-----
>> >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
>> >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
>> >.
>> >
>.
>
```
 0
anonymous (74722)
9/15/2004 7:05:10 PM
```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

llong@zaltec.com (Lindleman) wrote in message news:<e55a9638.0409151004.5d3bb509@posting.google.com>...
> 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
>
>
>
> "Alex" <anonymous@discussions.microsoft.com> wrote in message news:<133d01c49b02\$066811e0\$a601280a@phx.gbl>...
> > 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
> >
> > >-----Original Message-----
> > >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
> > >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
> > >.
> > >
```
 0
llong6590 (3)
9/16/2004 1:20:18 AM

Similar Artilces:

Please help! Displaying only rows with empty cells in a numeric field using Advanced Filter. Thanks
Folks I am learning how to use the Advanced Filter (Menus: Data, Filter, Advanced Filter). I can’t use Autofilter because in some cases I have to look for records that have to meet more than 2 conditions in the same field Some of the fields in the list that I am trying to filter have only numeric values. One of the conditions I am trying to define would allow me to filter the list exposing those rows in a numeric field that are blank (empty cells containing no data). I have tried using the following wildcard and formula combinations� <>* , =C11="" and <g … in the criter...

Avg. 1-month, 3-month, 6-month & 12-month Stock Returns
I don’t know for sure if Access can handle this request, but I know Queries are pretty powerful, so I’m thinking it is possible. As I alluded to in the title of the post, I’m trying to find a way to query for the average of 1-month stock returns, average of 3-month stock returns, average of 6-month stock returns & average of 12-month stock returns. This is my SQL now: SELECT SharePrices.StockSymbol, Avg(SharePrices.StockPrice) AS AveragePrice, tblStocksGroup.Company, tblStocksGroup.Group, tblStocksGroup.Class FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSy...

Retain cell references when column moves
Hi there Column C in a table contains data which is pulled into a chart: =Sheet1!C5:C13 I want the chart to pull data from column C even if I insert a new column to the left of Column C. In other words, when the current Column C data moves to the right and becomes Column D, I DON'T want the reference to change to =Sheet1!D5:D13 I want it to stay =Sheet1!C5:C13 and reflect the new values that are in the new column C. The Indirect function does this for a single cell, but I can't seem to make it work for a range of cells as shown above. Any way to do this? Every time a n...

Adding a second line on a two axis column stacked chart
I have a stacked bar graph showing 4 data series on the x- axis with a line showing 1 data series on the secondary x-axis. Now I want to add a second line to the secondary axis When I do so it automatically becomes a bar instead of a line, regardless which axis I am plotting it on. Is there anyone out there that knows what I am doing wrong and/or what the trick is ? The trick is this: Don't use the "built-in custom" chart types, but make your own. Make your chart with all series as stacked columns, then select each one you want to change, and use Chart Type on the Ch...

Return cell based on another cell help!
Hi, I have a spreadsheet with a list of codes in column A, a Master list of codes in column C, and a Master description in D that is associated with the values in C. If a code exists in A, I would like to check if the same code exists in C. If so, I would like to populate column B with the description in D. Can anyone show me how to do this please? Try this in B1: =IF(ISNA(MATCH(A1,\$C\$1:\$C\$100,0)),"No Match",VLOOKUP(A1,\$C\$1:\$D\$100,2,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============...

Finding thing errors quickly in a huge column
OK guys. I have a column with 20,000 dates in. They should all be in the format that is DD/MM/YYYY, but some times people have only put days and months and occasionally random characters are in there too. Is there a formula to search for cells in column A that are not DD/MM/YYYY? I have tried using autofilter and stuff but that is not very quick. Many thanks gang! ...

Columns labeled as numbers, not letters!
Help! I have office 97, and the columns are labeled as 1, 2, 3, 4 rather than A, B, C, D. The rows are still labled as numbers. This makes working with formulas and spreadsheets too frustrating! Thanks! Go to Tools > Options > General tab and uncheck the box for "R1C1 reference style". HTH Jason Atlanta, GA >-----Original Message----- >Help! I have office 97, and the columns are labeled as 1, >2, 3, 4 rather than A, B, C, D. The rows are still labled >as numbers. This makes working with formulas and >spreadsheets too frustrating! > >Thank...

Deleting columns if value of cell is not matching
Hello All, I have one workbook which contains 20-30 sheets and from these i would like to check whether column C1="Expired" if value is matching then i would like to delete entire column from all the sheets except two sheet (sheet1 and sheet2). Please help in this regard.. Best regards Vikram You say " i would like to check whether column C1="Expired"...". In what sheet? Or do you mean you want to look in cell C1 In EACH sheet and, if found, delete Column C in THAT sheet? As written now, you say you want to delete column C of EVERY sheet ...

Auto fill column with sequential numbers with decimals
Want to auto fill as follows: 3, 3.1, 3.2, 3.3, 3.4 What method works? Can only seem to get sequence on primary number, example: 3.1, 4.1, 5.1 I want the number to the right of the decimal to change. Hi, Enter 3 in a1 Enter 3.1 in a2 Select both and drag down using the fill handle -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Dogstar" wrote: > Want to auto fill as follows: 3, 3.1, 3.2, 3.3, 3.4 > > What method works?...

Hello All, I am having a problem with Outlook 2003 and return read receipts that I was hoping some MS gurus might have an answer for. We use an offsite PoP3 server for mail (no exchange). Some users check mail from multiple locations or have it synced to their Treo's. To do this they have "Leave a copy of messages on the server" checked "Until deleted from deleted items" folder. This works fine except for when they receive a message with a return read receipt request. When this happens the user will continue to get a copy of the message everytime they check their ...

Moving to end of long columns
In other spreadsheets than Excel it is possible to paste a long range of data between columns by using the left or right column neighbour to get to the end of the target data range. I'll try to explain what I intend to do and I kindly ask more experienced users to tell me a working solution: - Imagine a column of about 60,000 rows in length (A1 ... A60000) - I intend to copy a cell to the empty column right of the 60,000 rows column - Let's presume the new cell data is already in the clipboard - I move to cell B1 - From B1 I move to A1 with Shift-LeftArrow - From there I intend...

Dynamic form, columns of subform based on a table that changes with each application
Hello! I want to make a form/subform that can be used across clinical trials. The form container for the subform won't change. However, the columns of the sub-form will change based on records in a table. I have a table of subjects with unique identifiers. I have a table of vital signs. The vitals collected for each study will be different. The vitals are collected at multiple time points. I want the subform to show the unique identifiers for the subjects as the rows and the columns to be the vital signs. Once collected on the form, I'd like to transpose the vitals into one vi...

is there a way to auto add rows in a work sheet when there are no more empty ROWS to be filed and there is more work space rows needed? i have a row with different colums with text to be filed in. what i want to do is when i am typing in the texts and reach the end of the last row it auto add a new ROW of the same settings as the last and if possible have my documentation continued there. You can use Data>Form to add new records to an existing table. It will automatically add the formulas to the new rows. Colin2u wrote: > is there a way to auto add rows in a work sheet when there ...

Is there a way to transfer/export columns, including user defined, on to another computer in Outlook Tasks? Jason No, custom views will not export. Try copying the .pst file and then open it on the new computer and drag and drop your tasks to the new tasks folder. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JB asked: | Is there a way to transfer/export columns, including user defined, on | to another computer in Outlook T...

PO and return
Raises a PO for 20 of the same Item Receives 20 for that item against that PO. Returns 11 of those items against the RCT. when he goes back to match invoice screen he wants the auto invoice to show only 9 items that he has received. However GP shows 20 items with regards to that invoice, any ideas ?? is there something might be missing No answers to that one either ? "Zille Hassan ( Support Consultant)." wrote: > Raises a PO for 20 of the same Item > Receives 20 for that item against that PO. > Returns 11 of those items against the RCT. > when he goes back to matc...

Subreport Group header printing/repeating twice
I have a main report with several subreports. Each subreport has its header information in a Group header, grouped by a constant expression, "=1", and the Repeat Section property is set to "yes." There is not a page header or report header on the subreports. Why does the Group header repeat itself on the first page of the report? It does not matter whether the report is previewed seperately or apart of the main report. I need the header to appear on each subsequent page, but not twice on the first page. ...

Freezing two columns in addition to Freeze Panes
Is there a way to Freeze two columns so that they do not scroll with the rest of the sheet? I need columns A and B to remain stationary when scrolling. Additionally, I need the normal Freeze Panes option to continue to be enabled at cell F3. Can this be done? Thanks, Paul Use a second window. -- HTH, Bernie MS Excel MVP "PCLIVE" <pclive@livenet.net> wrote in message news:ux7UcQjgFHA.3912@tk2msftngp13.phx.gbl... > Is there a way to Freeze two columns so that they do not scroll with the rest of the sheet? > > I need columns A and B to remain stationary when...

Performing an Action on a Range of Rows
Forgive the newbie question. I am looking to perform an action on a range of rows (ex. - COPY the contents of cell A1 to Cells K1 through K21,777 or DELETE Rows 2 through 10,801). Is there an easier way to do this than dragging the cursor down the column to the ending location? Thanks in advance...JohnB Copy A1. Click in NameBox to left of formula bar. Type K1:K21777 and ENTER. Edit>Paste. To delete rows enter the range A2:A10801 in the NameBox then Edit>Delete>Entire Row Gord Dibben MS Excel MVP On Mon, 23 Jul 2007 15:55:28 -0700, JohnB <bilecky@excite.com> wrote: ...

adding every other column
i need a formula in cell A1 to total cell C1 and everyother column after that i.e. E1,G1 ect i need a formula in cell B1 to total cell D1 and everyother column after that i.e. F1,H1 ect how do i do that? =SUMPRODUCT(MOD(COLUMN(C1:S1),2),C1:S1) in A1 =SUMPRODUCT(MOD(COLUMN(D1:T1)-1,2),D1:T1) in B1 Adjust the ranges to suit. -- David Biddulph "gma" <gma@mircosoft.com> wrote in message news:233B9A5E-149D-4110-92F0-706CFC5CF2BA@microsoft.com... >i need a formula in cell A1 to total cell C1 and everyother column after >that > i.e. E1,G1 ect > i n...

Stacked column graphs and text displayed inside the columns
I want to create a stacked column graph for a series of numbers, then have corresponding text appear inside the columns. Can this be done in Excel? If not, why do my text boxes disappear (get sent backward) when selecting my graph? You could try to use data labels. Right click a series, choose Format, and for data labels, choose one of the default options. You can edit the text of one of these default labels, or use a utility like one of these to add custom labels from elsewhere in the worksheet: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tool...

Listing months and years in two columns based on start and end date.
Hello Everyone, I freely admit to being an Excel idjut and I hope you all can help m out. What I would like to do is enter a start date in one cell and en date in another cell. Then I would like for the spreadsheet to lis the months in between the start date and end date in one column and th associated year to that month in the next column. For example, if my start date is May 2000 and my end date is July 2001 the columns should be May 2000, June 2000, July 2000 until the end dat is reached. Is there a way to do this? Thanks! KLyn -- Message posted from http://www.ExcelForum.com I fo...

Mixed up e-mail bodies and headers
I created a new folder in Outlook 2003 on Win XP and moved some old e- mails from inbox to this folder. Then I decided to create subfolders in this folder for every month and moved e-mails into them. After that, all e-mails have mixed up headers and bodies, i.e a body of e- mail from 2 weeks ago has a header of e-mail from yesterday. Seems like headers and bodies have been combined completely randomly in this subfolder. All other folders and inbox seem to be ok. Is there anything I can do to synchronize the old e-mails? Ta Koval <koval71830@gmail.com> wrote: > I created a new folde...

Returning null values
I have a report where I think that certain fields are not shrinking because while I think that the associated query fields look blank, they are no in fact null. How can I ensure that a query field (that is the result of an expression) is in fact null, and not just blank? Joseph Greenberg wrote: >I have a report where I think that certain fields are not shrinking because >while I think that the associated query fields look blank, they are no in >fact null. How can I ensure that a query field (that is the result of an >expression) is in fact null, and not just b...