Ok I realise that there was a previous thread with a similar subject, but
that was a) not completely answered and b) differed in a very important
aspect so please do not point me back to that one.

Sample Data:
Sheet 1:
Client Discipline Quality
C1 Art Good
C2 Audio Average
C1 Audio Poor
C4 Art Good

I am trying to seperate the data above (which is taken from an outside
source) into seperate worksheets. So there are 2 seperate worksheets for Art
and Audio. Example, the column headings in the "Art" tables are "Client" and
"Quality" in A2 and B2. A1 holds the word "Art".

The code that I have used in cell A2 is as follows:
=IF(COUNTIF(Sheet1!B$1:B$5,A$1)=ROWS($1:1),INDEX( Sheet1!A$1:A$5,
SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1) )),"")

However the formula only works for a table with 5 rows. Is it possible to
set it to look at an entire column? (i.e. B:B instead of B$1:B$5). I
bascially have no idea how many rows there will eventually be and I would
like to set a variable number of rows. Is this possible?

Hope someone can help...

Thanks