Make formula change when named range rows change?

[lmac]

If B9:B30 is a named range, how can I re-write my formulas in B2:B6 so that they change when there are more than 30 rows of data.

For ie; B2 formula is =MIN($B$9:$B$30) but the number of rows I might have fluctuates and could be ($B$9:$B$1500).

I know I can do this with a macro but how can it be done with a formula within the sheet itself.

Book1.xls
	A	B	C	D
1	If Sales	To	Score
2	< or =	695.00	-2
3	> or =	697.10	-1
4	> or =	705.50	0
5	> or =	789.50	1
6	> or =	797.90	2
7
8		Sales	Score
9		800.00	2
10		795.00	1
11		790.00	1
12		785.00	0
13		780.00	0
14		775.00	0
15		770.00	0
16		765.00	0
17		760.00	0
18		755.00	0
19		750.00	0
20		745.00	0
21		740.00	0
22		735.00	0
23		730.00	0
24		725.00	0
25		720.00	0
26		715.00	0
27		710.00	0
28		705.00	-1
29		700.00	-1
30		695.00	-2
Sheet1

 

[Brian from Maui]

You can use a dynamic named range. Go go Insert/Name/Define, and enter in Names in Workbook, List or whatever name you choose, in the Refers to window enter,

=$B$9:INDEX($B:$B,MATCH(9.99999999999999E+307,$B:$B)).

Now for the MIN use,

=MIN(List)

 

[lmac]

Thank you so much Brian. This worked PERFECTLY! Thanks again. I actually jumped up out of my seat and shouted.

This Case Closed