Purchase Order Number Generator

D

Dopey_

New Member
Joined
Mar 9, 2015
Messages
16
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
 
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Hopefully this will make things a little clearer, here is the example I linked in text:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 177"]
<tbody>[TR]
[TD="class: xl66, width: 177"]QUOTE REFERENCE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl66, width: 155"]SUPPLIER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl66, width: 107"]PURCHASING ORDER NUMBER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 131"]
<tbody>[TR]
[TD="class: xl66, width: 131"]DATE ORDERED FROM SUPPLIER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl66, width: 237"]PURCHASE ORDER GENERATOR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]AMAZON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl65, width: 107"]1000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 131"]
<tbody>[TR]
[TD="class: xl65, width: 131"]20/08/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-AMAZON-200815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]AMAZON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl65, width: 107"]1000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 131"]
<tbody>[TR]
[TD="class: xl65, width: 131"]20/08/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-AMAZON-200815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]EBAY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl65, width: 107"]1001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20/08/2015[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-EBAY-200815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1075[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]EBAY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1002
[/TD]
[TD]27/08/2015[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-EBAY-270715[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 177"]
<tbody>[TR]
[TD="class: xl65, width: 177"]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]EBAY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1001[/TD]
[TD]20/08/2015[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-EBAY-200815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]TOOLWORKS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1003[/TD]
[TD]20/08/2015[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-TOOLWORKS-200815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]FIREBOLTS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]BUTLINS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1004[/TD]
[TD]21/08/2015[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-BUTLINS-210815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl65, width: 155"]AMAZON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1005[/TD]
[TD]28/08/2015[/TD]
[TD][TABLE="width: 237"]
<tbody>[TR]
[TD="class: xl65, width: 237"]PO-AMAZON-280815[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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")))
 
Last edited:
Upvote 0
MAX(IF($AD$2:$AD3&$AD5:$AD1001

what does this part mean - is it the same as max(if(and(ad2:ad3,ad5:ad1001)< ....
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA to generate report
2
Replies
15
Views
302
dsubash
D
P
  • Question Question
Formula for calculating count based on date or month ranges
Replies
7
Views
1K
Pancakey
P
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
349
RobP
R
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
569
Fluff
Fluff
A
  • Question Question
Lookup and match part lines to a purchase order number
Replies
1
Views
460
ac7
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top