Help with school project please

GizmoLad · Apr 10th, 2002, 02:44 AM

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 · Apr 10th, 2002, 02:49 AM

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

GizmoLad · Apr 10th, 2002, 02:51 AM

I'm doing AS Level IT.

Derek · Apr 10th, 2002, 02:55 AM

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 · Apr 10th, 2002, 03:10 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 02:44 AM	#1
GizmoLad New Member Join Date: Apr 2002 Posts: 3	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

Apr 10th, 2002, 02:49 AM	#2
LMF Board Regular Join Date: Mar 2002 Posts: 73	just wondering, u doing gcse, as-level or a-level?

Apr 10th, 2002, 02:51 AM	#3
GizmoLad New Member Join Date: Apr 2002 Posts: 3	I'm doing AS Level IT.

Apr 10th, 2002, 02:55 AM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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

Apr 10th, 2002, 03:10 AM	#5
GizmoLad New Member Join Date: Apr 2002 Posts: 3	Derek, Thanks a lot mate. It works like a beauty!! I owe u one. Cheers Gizmo