Create a Column of sequential numbers for purchase orders, ignoring blank cells.

[nlitedesign]

Hi All,

I am trying to solve a problem, please help if you can. I bet it is a simple solution. I have current found the value of the last cell but obviously that updates.

I have a List of all my purchases sorted by date order in Column C2:C10, in Column B2:B10 I have cells for Purchase order (PO) numbers starting from 001.


The problem is however, that not all purchases have PO numbers, and I do not want to miss numbers out of the sequence...

Column A2:A10 is a Y/N and what I would like to happen is as below:

IF A2 equals Y, add 1 to the last PO number found above current row in column B, regardless of blank cells in between.

Please see table below.

Thank You in Advance


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Y
[/TD]
[TD]001
[/TD]
[TD]Item A
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Y
[/TD]
[TD]002
[/TD]
[TD]Item B
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]Item C
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Y
[/TD]
[TD]003
[/TD]
[TD]Item D
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]Item E
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]Item F
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Y
[/TD]
[TD]004
[/TD]
[TD]Item G
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Saba Sabaratnam]

Hi,

Copy your data in A1:D11 to understand the formula

Enter the following formula in E2

=IF(B2="Y",MAX($E$1:E1)+1,"")

Let me know how you go,

Kind regards

Saba

 

[jtakw]

Hi,

Assuming your PO Number is Text (because of the leading 0s), first PO number is Manually entered, B2 formula copied down:

Book1
	A	B	C
1	Y	001	Item A
2	Y	002	Item B
3	N		Item C
4	Y	003	Item D
5	N		Item E
6	N		Item F
7	Y	004	Item G
Sheet90

Cell Formulas
Range	Formula
B2	=IF(A2="Y",TEXT(LOOKUP(9.99999999999999E+307,0+B$1:B1)+1,"000"),"")

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Header1​

[/td][td]

Header2​

[/td][td]

Header3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Y​

[/td][td]

1​

[/td][td]

Item A​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Y​

[/td][td]

2​

[/td][td]

Item B​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

N​

[/td][td][/td][td]

Item C​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Y​

[/td][td]

3​

[/td][td]

Item D​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

N​

[/td][td][/td][td]

Item E​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

N​

[/td][td][/td][td]

Item F​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Y​

[/td][td]

4​

[/td][td]

Item G​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

B2 copied down
=IF(A2="Y",COUNTIF(A$2:A2,"Y"),"")

Format column B Custom 000

M.

 

[nlitedesign]

Hi Saba,

Thank you for your reply.

I am confused by what you mean in your message, your cell references are not what I was using.... Could you please elaborate?

I have solved it in one way for the moment.. by using your idea of MAX.. I added column D, and in D2 added =MAX($C$2:C2), then in Cell C3 added =IF($A3="Y",SUM($D2+1),""), and dragged those down..

This creates a running column of numbers that increase as a PO number is added....

Is this what you meant?

nlitedesign

 

[nlitedesign]

https://www.dropbox.com/s/1vp9izqi563colp/Snip.JPG?dl=0


This is what I have done currently which works.. I will hide Column D.

Thank you all very much for your help.

 

[Saba Sabaratnam]

Hi Nlitedesign,

Glad to see that you have sorted out it.

What I meant was that I copied the data in the range and created the formula to illustrate my formula so that you could adopt the formula to suit your cell references.



Kind regards

Saba

 

[jtakw]

If what you're saying in Post #5 and #6 is working for you, that tells me your PO numbers are Real Numbers formatted to show leading 0s.
If that's the case, why would you need a Helper Column at all?

Marcelo's formula in Post #4 will work, and so will this:

Book1
	A	B	C	D
1	Y		001	Item A
2	Y		002	Item B
3	N			Item C
4	Y		003	Item D
5	N			Item E
6	N			Item F
7	Y		004	Item G
Sheet93

Cell Formulas
Range	Formula
C2	=IF(A2="Y",MAX(C$1:C1)+1,"")