"ajaffer" wrote:
.. is it possible to do this without the DV. I tried removing it and
replacing the data with something else and it didnt work.

Of course. To clear the DV, just select the cell,
click Data Validation, click "Clear All" OK

.. replacing the data with something else and it didnt work

well, this could probably be due to extraneous white spaces creeping into
either the manual input made in Sheet2!$A$1, and/or white spaces present
within the data in the "Season" col in Sheet1. The extra white spaces (not
readily visible, especially trailing spaces or an extra space in-between
text) could be throwing the matching off. We could wrap TRIM() around both
to increase robustness of matching. TRIM will remove all the extra white
spaces

Try this. Replace the criteria formula in Sheet1's F2 with:

=IF(OR(TRIM(Sheet2!$A$1)="",TRIM(B2)=""),"",
IF(TRIM(Sheet2!$A$1)=TRIM(B2),ROW(),""))

Copy F2 down as before

Let me know how this worked out for you.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--