Named Ranges

[How_Do_I]

I have my teams in column A always starting in A2. There are 24 Teams in this example however other workbooks will have 16, 18 or 20 Teams.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I want to create 3 <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Named</st1:PlaceName> <st1:PlaceType w:st="on">Ranges</st1:PlaceType></st1lace>:
<o></o>
MaxArea
TopHalf
BottomHalf
<o></o>
So TopHalf and BottomHalf need to contain 12 Teams each when looking at a 24 Team workbook… 10 each when looking at a 20 Team workbook etc… Max Area needs to adjust accordingly too…
<o></o>
I’ve placed a MATCH in C8 and C21 so that my named ranges show in the Jeanie.
<o></o>
My question is… is there another way to create the formulas that give my named ranges as my REAL workbook has other data in Column A so I can't use A:A please ?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 97px"><COL style="WIDTH: 24px"><COL style="WIDTH: 110px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD>Number of Teams</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 8pt">Barnsley</TD><TD></TD><TD style="TEXT-ALIGN: center">24</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 8pt">Bristol City</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 8pt">Burnley</TD><TD></TD><TD style="TEXT-ALIGN: center">Half Teams</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt">Cardiff City</TD><TD></TD><TD style="TEXT-ALIGN: center">12</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt">Coventry City</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 8pt">Crystal Palace</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 8pt">Derby County</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 8pt">Doncaster Rovers</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 8pt">Hull City</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-SIZE: 8pt">Ipswich Town</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 8pt">Leeds Utd</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-SIZE: 8pt">Leicester City</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-SIZE: 8pt">Middlesbrough</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-SIZE: 8pt">Millwall</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-SIZE: 8pt">Norwich City</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="FONT-SIZE: 8pt">Nottingham Forest</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="FONT-SIZE: 8pt">Portsmouth</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="FONT-SIZE: 8pt">Preston North End</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="FONT-SIZE: 8pt">QPR</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="FONT-SIZE: 8pt">Reading</TD><TD></TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="FONT-SIZE: 8pt">Scunthorpe United</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="FONT-SIZE: 8pt">Sheffield United</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="FONT-SIZE: 8pt">Swansea City</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="FONT-SIZE: 8pt">Watford</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=COUNTA(A:A)</TD></TR><TR><TD>C5</TD><TD>=C2/2</TD></TR><TR><TD>C8</TD><TD>=MATCH(A4,TopHalf,0)</TD></TR><TR><TD>C21</TD><TD>=MATCH(A21,BottomHalf,0)</TD></TR></TBODY></TABLE></TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD>C8</TD><TD>MaxArea</TD><TD>=INDEX(Sheet1!$A:$A,2):INDEX(Sheet1!$A:$A,Sheet1!$C$2+1)</TD></TR><TR><TD>C8</TD><TD>TopHalf</TD><TD>=INDEX(MaxArea,1):INDEX(MaxArea,Sheet1!$C$5)</TD></TR><TR><TD>C21</TD><TD>BottomHalf</TD><TD>=INDEX(MaxArea,Sheet1!$C$5+1):INDEX(MaxArea,SUM(Sheet1!$C$5,Sheet1!$C$5))</TD></TR><TR><TD>C21</TD><TD>MaxArea</TD><TD>=INDEX(Sheet1!$A:$A,2):INDEX(Sheet1!$A:$A,Sheet1!$C$2+1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[pgc01]

Hi

If I understand correctly, your only problem is to determine the number of teams (in C2), the rest is working?

Assuming you always leave one empty cell in column A after the list of teams, in C2:

=MATCH(TRUE,INDEX(A2:A26="",0),0)-1

 

[How_Do_I]

HI PCG... No I'm not after that...

I've used my actual range of interest in my Jeanie... My first return always starts in AP2... This example has 24 returns, other workbooks will have for example, 20 rows of data starting in AP2.

In all my books that W will be in AP27 and there's other data below this cell in Column AP:AP

What I'm after is the Index():INDEX() construction to name my range in AP2 down to AP25 in this instance. That range needs to be dynamic so it adjusts to 20 rows of data in column AP:AP when there are 20 rows of data there... Does that make sense?

Excel Workbook
	AP
1	HomeCode
2	29
3	#N/A
4	41
5	2
6	43
7	31
8	24
9	18
10	19
11	21
12	37
13	34
14	40
15	Ignore
16	8
17	36
18	4
19	14
20	16
21	13
22	28
23	10
24	11
25	5
26
27	W
Mini Leagues

#VALUE!

</td></tr></table></td></tr></table>

 

[pgc01]

HI PCG... No I'm not after that...

I've used my actual range of interest in my Jeanie... My first return always starts in AP2... This example has 24 returns, other workbooks will have for example, 20 rows of data starting in AP2.

In all my books that W will be in AP27 and there's other data below this cell in Column AP:AP

What I'm after is the Index():INDEX() construction to name my range in AP2 down to AP25 in this instance. That range needs to be dynamic so it adjusts to 20 rows of data in column AP:AP when there are 20 rows of data there... Does that make sense?

Well, 2 things,

1 - I still think it all boils down to the number of teams

If you have the number of teams in C2 the range is

$AP$2:INDEX($AP$2:$AP$26,$C$2)

where C2 is the number of teams (the formula I posted before).

2 - I'm taking for granted that in the range you have the name or number of the teams, as you explained in your posts. There are strange values in AP3 and AP15 that I assumed were mistakes in the data when posting.

 

[How_Do_I]

Well, 2 things,

1 - I still think it all boils down to the number of teams

If you have the number of teams in C2 the range is

$AP$2:INDEX($AP$2:$AP$26,$C$2)

where C2 is the number of teams (the formula I posted before).

2 - I'm taking for granted that in the range you have the name or number of the teams, as you explained in your posts. There are strange values in AP3 and AP15 that I assumed were mistakes in the data when posting.

Hi PGC...

Firstly I've used your formula in this post so thank you for that... <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I confused myself and it looks like you too with the range that used words and number as opposed to club names... There was nothing else in the column other than those club names.<o></o>
<o> </o>
I used the ranges and data in my last Jeanie as I mistakenly thought my formulas would count data in the columns incorrectly because of the data below the ranges I've shown... This isn't the case and I've combined your formula to the ones I had to get this running... Thanks again.<o></o>