Circular reference from a IF,Match formula

[NileUk]

Hi guys

Trying to get a formula to work, and failing....again!

So I've got this far and can't understand why its not working, did tests as I went along, but now it's coming up with a result of '0' Dunno why?

The Formula is as follows


Excel Formula:

=IF(AND(MATCH(C5,[Quote '#],0),([Customer Name]=C6)),MAX([Customer '#]),MAX([Customer '#])+1)

I haven't started on the formula for the Job # cells yet but if you could point me in the right direction I would greatly appreciate it.

heresthe xl2bb sheet

Help please.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
3	Workbook 'Pricing'			Workbook 'Database'
4				Table1
5	Quote #	Q10404		Quote #	Customer #	Customer Name	Job #	# of engineers	hourly Rate	Total Hours	Job Value	Material Costs
6	Customer	Meg Ryan		Q10395	45051	Fred Dibner	1	1	£ 20.00	16	£ 360.00	£ 40.00
7	hourly Rate	£ 25.00		Q10396	45052	Peter Jones	1	1	£ 25.00	4	£ 186.00	£ 86.00
8	# of engineers	2		Q10397	45051	Fred Dibner	2	2	£ 20.00	32	£ 820.00	£ 180.00
9	total hrs	50		Q10398	45053	Alexi Sale	1	2	£ 25.00	24	£ 860.00	£ 260.00
10	Material Costs	£ 250.00		Q10399	45051	Fred Dibner	3	1	£ 20.00	4	£ 95.00	£ 15.00
11	Job Value	£ 1,500.00		Q10400	45054	John Belushi	1	1	£ 25.00	8	£ 237.00	£ 37.00
12				Q10401	45055	Joan Armatrading	1	2	£ 20.00	16	£ 389.00	£ 69.00
13				Q10402		Fred Dibner					£ -
14				Q10403	45051	Fred Dibner	4	1	£ 20.00	48	£ 1,170.00	£ 210.00
15				Q10404	0	Meg Ryan					£ -
16				Q10405	45056	Neil Young	1	2	£ 25.00	16	£ 550.00	£ 150.00
17				Q10406	45057	Meg Ryan	1	1	£ 25.00	16	£ 539.00	£ 139.00
18				Q10407	45053	Alexi Sale	2	1	£ 25.00	8	£ 269.00	£ 69.00
19				Q10408	45057	Meg Ryan	2	1	£ 25.00	4	£ 145.00	£ 45.00
20				Q10409		John Belushi					£ -
21				Q10410	45051	Fred Dibner	5	1	£ 20.00	4	£ 110.00	£ 30.00
22
Sheet1

Cell Formulas
Range	Formula
C11	C11	=C9*C7+C10
F15	F15	=IF(AND(MATCH(C5,[Quote '#],0),([Customer Name]=C6)),MAX([Customer '#]),MAX([Customer '#])+1)
L6:L21	L6	=K6*J6+M6

Kind Regards

Nile

 

[Fluff]

Duplicate to: If/Match/Max formula

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.