Simon Lloyd
01-22-2007, 04:25 PM
Hi all i have the formula below to check for duplicate names in a range which works fine
=IF(COUNTIF(Crewing!B$1:Q33,A3)>1,"Duplicated","Ok")what i want to be able to do is have a formula on another worksheet that will display the offset name if "Duplicated" is found, below is something i tried but of course doesnt work!
=if('Names & Areas'!B3:B89="Duplicated",(offset(,0,-1,,)),"")also if the cell containing this formula gives the duplicated name is it possible to also display the duplication cell address?
It is possible that 5 or 6 names may be duplicated so i would need to copy the formula down 5 to 6 cells enabling each duplication to be shown any ideas?
Regards,
Simon
P.S i realise this is an easy fix in VBA but would like a worksheet formula solution.
=IF(COUNTIF(Crewing!B$1:Q33,A3)>1,"Duplicated","Ok")what i want to be able to do is have a formula on another worksheet that will display the offset name if "Duplicated" is found, below is something i tried but of course doesnt work!
=if('Names & Areas'!B3:B89="Duplicated",(offset(,0,-1,,)),"")also if the cell containing this formula gives the duplicated name is it possible to also display the duplication cell address?
It is possible that 5 or 6 names may be duplicated so i would need to copy the formula down 5 to 6 cells enabling each duplication to be shown any ideas?
Regards,
Simon
P.S i realise this is an easy fix in VBA but would like a worksheet formula solution.