Sequential numbering with gaps

[tghcogo]

Hi there.


I have a column in which I enter a date, and an adjacent column which automatically enters a sequential number, using ...

Please Login or Register  to view this content.

this is fine providing every line has an entry, however I know want the same thing to happen if there are blanks in the date column:

Please Login or Register  to view this content.

I've tried several ways, but I think I'm overcomplicating, hopefully someone can point me in the right direction with a simple solution.

Thanks

[mikerickson]

For the indexing column
in A2 (copied down)
=COUNTA(A$1:A1)+MIN(A$1:A1)

A1 would hold the starting value.

Note that this works for blank Cells, cells containing "" will foul it.

[tghcogo]

Hmm, perhaps I'm doing something wrong,

it returns

801 10-Jul
802 11-Jul
803
804 12-Jul
805
806 13-Jul
807
808
809 1

I have also decided to not use the date column, but a text column, so what I want it to do is

801 CMS
802 L&Y
Blank Blank
803 Regal
Blank Blank
804 CMS
Blank Blank
Blank Blank
Blank Blank

Thanks

[mikerickson]

=COUNTA(B$1:B1)+MIN(A$1:A1)
might do what you want. It will result in

801 date
802 date
802 <blank>
802 <blank>
803 date
804 date

so (in A3) you might want =IF(ISBLANK(B3),"",COUNTA(B$1:B3)+MIN(A$1:A3)

[daddylonglegs]

If your data starts at K10 then in J10 copied down try

=IF(K10="","",COUNTIF(K$10:K10,"?*"))

[Leith Ross]

Hello tghcogo,

Please remember to wrap your code. It makes it easier for everyone to read, and follow. Next time, your post will be locked until you do wrap your code.

Thanks,
Leith Ross

[tghcogo]

Sorry, how do I do that?

[tghcogo]

The first sequential number is going to be somewhere between 590 and 620 because of historic data, so let's say 601. I tried adding that to the formula but it returned #VALUE where there were blanks.

[daddylonglegs]

If you want to start at 601 then try this formula in J10 copied down

=IF(K10="","",COUNTIF(K$10:K10,"?*")+600)

[tghcogo]

Thanks, it works a treat.

As a matter of interest what does the "?*" do.

[daddylonglegs]

Thanks, it works a treat.

As a matter of interest what does the "?*" do.

It's just a way of counting text without counting "formula blanks". If your blanks are genuinely blank [ and not formulas that generate ""] then you could use COUNTA which counts text and numbers....[but also formula blanks]