Conditional Number Sequencing

[Dan Johnson]

I am trying to automatically increment a column of numbers if a second
column is populated, otherwise the auto-numbered cell will be empty if
the cell defining it to be blank is also blank. As an example:


Ticket # Ticket Invoice # Cost

7325689 Y 1234243 $ 48.78
1234244 $356.50
7325690 Y 1234245 $123.32
7325691 Y 1234246 $435.89

So, if the Ticket column is not populated, the Ticket # column will not
increment. In the case of row 2 of data where row 2, column "Ticket" is
not populated with a Y, then there is no increment of Ticket number.
However, when row 3, is populated with Y, the ticket cell increments 1
number from row 1, not row 2. I understand the IF statement but I need
the means by which to look at multiple cells above until it finds the
last ticket # and add 1 to that number.

Is there a way to accommodate the above scenario? Thanks, in advance,
for the help

 

[Bernie Deitrick]

Dan,

If your data starts in column A, row 2, then in cell A2 use the
formula

=IF(B2="Y",MAX($A$1:A1)+1,"")

and copy it down as far as you need.

HTH,
Bernie

 

[Justin Oakley]

-----Original Message-----
Dan,

If your data starts in column A, row 2, then in cell A2 use the
formula

=IF(B2="Y",MAX($A$1:A1)+1,"")

and copy it down as far as you need.

HTH,
Bernie

This would only work if the invoice numbers ran in
ascending order which they will not as they are not
allocated in row sequence.
Also one really wants value to be assigned to the cell and
not as a result of a calculation.
I suggest a macro to run down the list of tickets and
allocate a invoice number when it find a ticket that has
been issued and not invoiced. You would need a cell that
stores the next invoice number to use. Can you write this
code? Shout if not.

 

[Dan Johnson]

Bernie,

While I agree with the comments from Justin, your solution did the
trick. There is one anomalyt that I would like to enhance. As the
updated record is sequencing, it takes an unexpected amount of time to
update. I have the first 4993 cells in the column with your formula.
Is there a means to speed up the process? Thanks for the initial
response and thanks, in advance, for any additional assistance.

Dan