Purchase Order Number Generator

[Dopey_]

I've been trying to make a purchase order number generate automatically using excel. I got it down so far and have hit a brick wall. The formula I'm using at the minute is

=IF(OR($E5="",ISNA(IF(COUNTIF($E$2:$E5,$E5)=1,MAX(IF($C$2:$C4<40000,$C$2:$C4))+1,INDEX($C$2:$C4,MATCH($E5,$E$2:$E4,0))))),"",IF(B5="",""))))

Bacially, on my table (that I will Hyperlink to) I want to go by the date the order would be placed so the Number isn't then restricted to being in chronological order. I tried this and then it came up with circular references for fun.

I hope I've explained this properly using my table.

http://www.filedropper.com/exampleforordernumber

 

[oldbrewer]

so on august 15 the number might be 8- 835691

why do you not want to have sequential numbers eg last july one = 7-126

first august one 8-127

 

[Dopey_]

Hi Oldbrewe,

Not my call to make to change the way we do Order Numbers in the company I work for, I agree it would be a quick solution to the problem, but unfortunately the numbers need to keep in sequential order to not confuse the staff. It sounds silly I know

Thank you for replying

 

[oldbrewer]

I never download from filedropper etc so I cannot see your data.

the month and year followed by a sequential number WOULD work - eg if I put in a purchase order for 10000 widgets today for delivery September 10th, you can either use sept 1 or sept 10 eg 9-2015-10479

but I cannot see what you want

 

[Dopey_]

I've had a look and the part of the formula that's making the circular references is the IF(MAX part. The formula I have at the minute is:

=IF(COUNTIF($AL$2:$AL4,$AL4)=1,IF($I4="",MAX(IF($AB$2:$AB3<40000,$AB$2:$AB3))+1,MAX(IF($AD$2:$AD3&$AD5:$AD1001<$AD4,$AB$2:$AB3&$AB5:$AB1001))+1),INDEX($AB$2:$AB3,MATCH($AL4,$AL$2:$AL3,0)))

Unfortunately, the part of it that's going wrong is this:

MAX(IF($AD$2:$AD3&$AD5:$AD1001<$AD4,$AB$2:$AB3&$AB5:$AB1001))+1)

I am trying to MAX everything in the same column, but by missing out the cell I'm using. Otherwise it just comes up as a circular reference. Is there a better way to write maxif to look at the same column that contains the cell you're using?

 

[Dopey_]

Hopefully this will make things a little clearer, here is the example I linked in text:

QUOTE REFERENCE <tbody> </tbody>	SUPPLIER <tbody> </tbody>	PURCHASING ORDER NUMBER <tbody> </tbody>	DATE ORDERED FROM SUPPLIER <tbody> </tbody>	PURCHASE ORDER GENERATOR <tbody> </tbody>
	AMAZON <tbody> </tbody>	1000 <tbody> </tbody>	20/08/2015 <tbody> </tbody>	PO-AMAZON-200815 <tbody> </tbody>
	AMAZON <tbody> </tbody>	1000 <tbody> </tbody>	20/08/2015 <tbody> </tbody>	PO-AMAZON-200815 <tbody> </tbody>
	EBAY <tbody> </tbody>	1001 <tbody> </tbody>	20/08/2015	PO-EBAY-200815 <tbody> </tbody>
1075	EBAY <tbody> </tbody>	1002	27/08/2015	PO-EBAY-270715 <tbody> </tbody>
<tbody> </tbody>	EBAY <tbody> </tbody>	1001	20/08/2015	PO-EBAY-200815 <tbody> </tbody>
	TOOLWORKS <tbody> </tbody>	1003	20/08/2015	PO-TOOLWORKS-200815 <tbody> </tbody>
	FIREBOLTS <tbody> </tbody>
	BUTLINS <tbody> </tbody>	1004	21/08/2015	PO-BUTLINS-210815 <tbody> </tbody>
	AMAZON <tbody> </tbody>	1005	28/08/2015	PO-AMAZON-280815 <tbody> </tbody>

<tbody>
</tbody>

Columns A, B & D are manual.

C starts with manual number 1000, then column below is formula:=IF(COUNTIF($E$2:$E$3,$E3)=1,IF($A3="",MAX(IF($C$2:$C2<40000,$C$2:$C2))+1,MAX(IF($D$2:$D2&$D4:$D1000<$D3,$C$2:$C2&$C4:$C1000))+1),INDEX($C$2:$C2,MATCH($E3,$E$2:$E2,0)))

Column E is formula: =IF(D2="","",CONCATENATE("PO-",B2,"-",TEXT(D2,"DDMMYY")))

 

[oldbrewer]

MAX(IF($AD$2:$AD3&$AD5:$AD1001

what does this part mean - is it the same as max(if(and(ad2:ad3,ad5:ad1001)< ....