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.

2 vs 10 </SPAN>	BOARD 1</SPAN>
8 vs 12</SPAN>	BOARD 2</SPAN>
4 vs 6</SPAN>	BOARD 3</SPAN>
1 vs 9 </SPAN>	BOARD 4</SPAN>
7 vs 11</SPAN>	BOARD 5</SPAN>
3 vs 5</SPAN>	BOARD 6</SPAN>

<TBODY>
</TBODY>

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
18	2	vs	10	Board 1
19	8	vs	9	Board 2
20	4	vs	1	Board 3
21	6	vs	9	Board 4
22	7	vs	11	Board 5
23	3	vs	5	Board 6

<TBODY>
</TBODY>

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


Team1

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

<TBODY>
</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
1	The Red Arrows	*	*
2	*	*	The Way Out
3	Board 1	*	*

<TBODY>
</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
1	Team No.	Team
2	1	The Red Lion
3	2	The Red Arrows
4	3	The Kings Arms
5	4	The Kings Legs
6	5	The Hope & Anchor
7	6	The No Hope & *anker
8	7	The Rose & Woodbine
9	8	The Flower & Ciggy
10	9	The Navigation Inn
11	10	The Way Out
12	11	The Dog & Duck
13	12	The *Duck & Dog

<TBODY>
</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.