mordred: thanks a million for the code
it does exactly what i asked for
but erm... i've only realised that my description wasn't good enough

- i should have mentioned that i need to do wildcard searches - so any cell in column D containing the text "abc def" should have cell J in the same row replaced with "=abc!d2"

- my experience of vba and excel is very very limited
i don't even know how to open up the vba part and enter the code u gave!
so, what i've done is created an empty macro and assigned a keyboard short cut
then i've inserted the code u gave

this worked ok with a dummy spreadsheet
but the spreadsheet i need to use on has 4 different worksheets and the code wouldn't quite work

let me know if u could suggest anything else

thanks


om