Help with school project please

[GizmoLad]

Hi,

WOW, This site, and this message board is awesome.

I'm doing a project for school, its a Loans System for the school sports dept.
There are 3 sections to the worksheet,the user interface,the loans details,and repairs details.
Serial numbers of equipment on loan are held in cells L29-L50, and serial numbers of equipments on repair are held in cells B88-B100.

When somebody comes along and and issues a piece of equipment out for loan, they need to type in the serial number in cell D12(This is the user interface part of the sheet).

I've been trying to create a formula that checks this serial number in D12, with the serial numbers in the two other ranges, and comes up with the word "Error" if the serial number is present somewhere else between the two ranges.

I've tried and tried, but to no avail.

Please could you help me out,

Thanks,
Gizmo

 

[LMF]

just wondering, u doing gcse, as-level or a-level?

 

[GizmoLad]

I'm doing AS Level IT.

 

[Derek]

Hi Gizmo

If the number in D12 must not appear in range B88:B100 or in range L29:L50, then try this formula.

=IF(COUNTIF($B$88:$B$100,$D$12)+COUNTIF($L$29:$L$50,$D$12)>0,"ERROR","")

regards
Derek

 

[GizmoLad]

Derek,
Thanks a lot mate. It works like a beauty!!
I owe u one.
Cheers
Gizmo