Generating sequential numbers on a spreadsheet

Question

Dear All,

I am trying to incorporate a dynamic array which will generate sequential numbers on my spreadsheet.

I have the following syntax:

TEXT (SEQUENCE (COUNTA (A: A)-1 ) , " CWCB2026 - 000"

This string generates a #CALC! Error if cell A is blank but generates a #VALUE! Error if call A is populated.

I would be grateful for some help identifying where I am going wrong.

Kind Regards,

[Moderators note: personal information removed]

Answer 1

Hi @Thunder Hill,

Thank you for your prompt update. It helps me clearly understand your request.

The formula I suggested you in my initial response creates a spilled array, not a row-by-row formula and especially when using it inside an Excel table, it returns a SPILL error like this when the second value is populated:

In this case, you can try this revised formula (Note: change the "Header" into the header's name of your working sheet to make it works properly since the formula is based on my testing worksheet):

=IF([@Header]="","","CWCB2026 - " & TEXT(COUNTIF(INDEX([Header],1):[@Header],"<>"),"000"))

Hope this will work properly this time. Please don't hesitate to reach out if you need any further assistance. I'm happy to help.

Answer 2

TEXT (SEQUENCE (COUNTA (A: A)-1 ) , " CWCB2026 - 000"

This string generates a #CALC! Error if cell A is blank but generates a #VALUE! Error if call A is populated.

I would be grateful for some help identifying where I am going wrong.

---

Hi. I am a user, and not an A.I. bot.

The value error is caused by formatting a number with "B".

One solution is to use double quotes around your text.

=TEXT(SEQUENCE(COUNTA(A:A)-1),"""CWCB2026"" - 000")

Answer 3

Hi @Thunder Hill,

Please notify me if there is any part that I have misunderstood about your situation.

According to your concern, let me help you explain each result error and why it happens:

1/ The #CALC! when A is blank

The reason is because of this part SEQUENCE(COUNTA(A:A)-1)

• If column A is blank, COUNT(A:A) will return 0
• As a result, the function will be SEQUENCE(-1) => #CALC! error since SEQUENCE can't spill array with 0 or negative value

2/ The #VALUE! when A has value

• The reason is because TEXT() function expects a valid number format. Therefore, when you use "CWCB2026 - 000" inside TEXT(), Excel tries to apply CWCB2026 as a numeric format > fails > returns #VALUE! error

Based on your original formula, you can try using this revise version:

=IF(COUNTA(A:A) <= 1,"","CWCB2026 - " & TEXT(SEQUENCE(COUNTA(A:A)-1), "000"))

If there is no data, the formula returns blank. If column A contains value like A1 = Header ; A2, A3, A4 = Item > the formula will automatically spill the item code like this:

Hope my sharing may be helpful with your concern. You can try the formula and let me know if it works. If not, we can work together to find a solution.

If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you. 

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread