It appears that your CC numbers are in ascending order.

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=IF(ROWS($1:1)<=COUNTIF('Team Numbers'!D$2:D$8,"0"),SMALL(IF('Team
Numbers'!D$2:D$80,'Team Numbers'!A$2:A$8),ROWS($1:1)),"")

Copy down until you get blanks

Biff

"Nikki" wrote in message
...
Hi,

I have a table in on worksheet called 'Team Numbers' that has the
following:
CC Team No Deapartment L4 L3
11 11 Finance 9 2
14 4 IS 4
15 4 IS 4
16 0 Networks
31 0 HR
211 10 Networks 10
213 7 Networks 7

I have another worksheet called 'Site Allocation'
In the sheet I want to have a formula that does something like this:
In 'Team Numbers' look at first row in column 4 (L4) if this is 0 then
put
the number from column 1 (CC). If the number in column 4 is 0 or blank
then
I don't want it to put anything. I then want it to do the same thing
again
but for the second row.
The list that appears in the 'Site Allocation' sheet needs to only have
the
CC's listed that have numbers in L4 but I don't want any gaps.
e.g
11
14
15
211
213

Any ideas?