Help with formulas

[Jerk24]

I run a 12 team dart league and need a little (or alot) of help

i have a sheet for each team, and a team standings sheet. on the team standing i copy that weeks schedule.
[TABLE="width: 141"]
<TBODY>[TR]
[TD]2 vs 10 </SPAN>
[/TD]
[TD]BOARD 1</SPAN>
[/TD]
[/TR]
[TR]
[TD]8 vs 12</SPAN>
[/TD]
[TD]BOARD 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]4 vs 6</SPAN>
[/TD]
[TD]BOARD 3</SPAN>
[/TD]
[/TR]
[TR]
[TD]1 vs 9 </SPAN>
[/TD]
[TD]BOARD 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]7 vs 11</SPAN>
[/TD]
[TD]BOARD 5</SPAN>
[/TD]
[/TR]
[TR]
[TD]3 vs 5</SPAN>
[/TD]
[TD]BOARD 6</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

above is ('Team Standing'D18:E23)

From the i want to take the teams opponent and what board they play on to auto fill in specified cells the oppenent in C2, and where they play (board #) in A3 , each teams sheet is identical. nothing i have thought works.

 

[Snakehips]

Jerk24,
It may be that I am not fully understanding your requirements but perhaps this will help?
I am hoping that you are using Excel 2007 or 2010 otherwise the formulas will need to be changed and will be more cumbersome.

I have used 3 columns in the Standings sheet so that the team numbers are in a discrete cell.
There is also a Teams sheet to identify a team name by number.
Just edit the 'MATCH' part of the formulas in each team sheet to be the appropriate team numbe.r

Standings

*	B	C	D	E
2	vs	10	Board 1
8	vs	9	Board 2
4	vs	1	Board 3
6	vs	9	Board 4
7	vs	11	Board 5
3	vs	5	Board 6

<TBODY>
[TD="bgcolor: #cacaca"]18
[/TD]

[TD="bgcolor: #cacaca"]19
[/TD]

[TD="bgcolor: #cacaca"]20
[/TD]

[TD="bgcolor: #cacaca"]21
[/TD]

[TD="bgcolor: #cacaca"]22
[/TD]

[TD="bgcolor: #cacaca"]23
[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Team1

*	A	B	C	D
The Red Lion	*	*	*
*	*	The Kings Legs	*
Board 3	*	*	*

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula A1 =Teams!B2 C2 =VLOOKUP(IFERROR(INDIRECT("Standings!D"&MATCH(1,Standings!B18:B23,0)+17),INDIRECT("Standings!B"&MATCH(1,Standings!D18:D23,0)+17)),Teams!A2:B13,2) A3 ="Board "&IFERROR(MATCH(1,Standings!B18:B23,0),MATCH(1,Standings!D18:D23,0)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Team2

*	A	B	C
The Red Arrows	*	*
*	*	The Way Out
Board 1	*	*

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula A1 =Teams!B3 C2 =VLOOKUP(IFERROR(INDIRECT("Standings!D"&MATCH(2,Standings!B18:B23,0)+17),INDIRECT("Standings!B"&MATCH(2,Standings!D18:D23,0)+17)),Teams!A2:B13,2) A3 ="Board "&IFERROR(MATCH(2,Standings!B18:B23,0),MATCH(2,Standings!D18:D23,0)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Teams

*	A	B
Team No.	Team
The Red Lion
The Red Arrows
The Kings Arms
The Kings Legs
The Hope & Anchor
The No Hope & *anker
The Rose & Woodbine
The Flower & Ciggy
The Navigation Inn
The Way Out
The Dog & Duck
The *Duck & Dog

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]
[TD="align: right"]1
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]
[TD="align: right"]2
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]
[TD="align: right"]3
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]
[TD="align: right"]4
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]
[TD="align: right"]5
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]
[TD="align: right"]6
[/TD]

[TD="bgcolor: #cacaca"]8
[/TD]
[TD="align: right"]7
[/TD]

[TD="bgcolor: #cacaca"]9
[/TD]
[TD="align: right"]8
[/TD]

[TD="bgcolor: #cacaca"]10
[/TD]
[TD="align: right"]9
[/TD]

[TD="bgcolor: #cacaca"]11
[/TD]
[TD="align: right"]10
[/TD]

[TD="bgcolor: #cacaca"]12
[/TD]
[TD="align: right"]11
[/TD]

[TD="bgcolor: #cacaca"]13
[/TD]
[TD="align: right"]12
[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Hope that helps.

 

[Jerk24]

thank you. I will do some adjusting and work on it in the AM

 

[Jerk24]

OK so i fixed my schedule to match your example which will move to Team Standings!c18:f23

then i do i completely remove the "Match" with the team number or do I add the number "1" after Match.

i also put a small spreadsheet together if i can email it or attach it here somehow?

let me know

Thanks again

 

[Snakehips]

On the sheet for Team 1 the 'MATCH' formulas should be MATCH(1,......
On the sheet for Team 2 the 'MATCH' formulas should be MATCH(2,......
On the sheet for Team 3 the 'MATCH' formulas should be MATCH(3,...... etc for all 12 team sheets.

You cannot attach a file to the forum.
If you wish to send me your file then I will send you a PM with an email address.

 

[Jerk24]

Ok, this Formula =VLOOKUP(IFERROR(INDIRECT("Standings!D"&MATCH(2,Standings!B18:B23,0)+17),INDIRECT("Standings!B"&MATCH(2,Standings!D18:D23,0)+17)),Teams!A2:B13,2) (you wrote)


(Changed)--


=VLOOKUP(IFERROR(INDIRECT("Standings!E"&MATCH(1,Standings!C18:C23,0)+17),INDIRECT("Standings!C"&MATCH(1,Standings!E18:E23,0)+17)),Standings!E18:E23,1)</SPAN>

this worked for Team 1, when i copy to sheet team 2. change Match(2,.... NO Go.

I will keep working on it. any suggestions?

 

[Snakehips]

Jerk24,

Have you intentionally changed the tail end of the formula, shown here in red?
=VLOOKUP(IFERROR(INDIRECT("Standings!E"&MATCH(1,Standings!C18:C23,0)+17),INDIRECT("Standings!C"&MATCH(1,Standings!E18:E23,0)+17)),Standings!E18:E23,1)

If you are not using the Teams sheet that I proposed in my first post then that is fine provided that you can somehow reference each team names by numbers 1 to 12 in a continuous list. If you do not construct it correctly you will struggle with the lookup.

I will PM you and you can mail me your file if you wish.