Hide previously used items in dropdown list?

[danbates]

Hi,

I have copied a dropdown list off the internet and it works fine when on one sheet(Sheet3), but I would like to have all the data on Sheet3 and then the cells with the list on Sheets1 but I can't get it to work correctly.

This is what I have when its all on one sheet:

Col A with all the names 33 in total.

Col B

=IF(COUNTIF($F$4:$L$24,A1)>=1,"",ROW())

Col C

=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$33),"",INDEX(A:A,SMALL(B$1:B$33,1+ROW(A1)-ROW(A$1))))

and then in the Name manager I have this

=OFFSET(Sheet3!$C$1,0,0,COUNTA(Sheet3!$C$1:$C$33)-COUNTBLANK(Sheet3!$C$1:$C$33),1)

but when I put all the data on Sheet3 and then the dropdown list on Sheet1, only when I select the first name does it work right.
This is the only part of the code I changed thinking it would work.

=IF(COUNTIF(Sheet1!B2:F19,A1)>=1,"",ROW())

I hope this makes sense and any help would be appreciated.

Thanks

Dan