# Using row(indirect(start&”,”&end))

#### liabilityquek

##### Member
Hi all

I have two numbers 4119000501 and 4119000510 in cell A1 and A2 respectively. I would like to identify the list of numbers showing 4119000501 to 4119000510 (showing 10 numbers). So I used the formula {row(indirect(A1&”,”&A2))} and it doesn’t work.

As per attached

#### Attachments

• 9 KB Views: 5

#### bosco_yip

##### Excel Ninja
To create an array of numbers, you can use an array formula based on the ROW and INDIRECT functions.

{=ROW(INDIRECT(start&":"&end))}

The maximum number of the ROW formula can create is 1,048,576 (Excel max row number)

Your number 4119000501 appear exceeding the max row number, and the workaround array formula can be done by :

{=40119000500+ROW(INDIRECT("1:10"))}

and will return ( in pressing F9 ) :

>>

=40119000500+{{1;2;3;4;5;6;7;8;9;10}

>>

{40119000501;40119000502;40119000503;40119000504;40119000505;40119000506;40119000507;40119000508;40119000509;40119000510}

Regards

Last edited:
• herofox

#### liabilityquek

##### Member
To create an array of numbers, you can use a array formula based on the ROW and INDIRECT functions.

{=ROW(INDIRECT(start&":"&end))}

The maximum number of the row formula can create is 1,048,576 (Excel max rows number)

Your number 4119000501 appeared exceeding the max rows number, the workaround array formula can be done by :

{=40119000500+ROW(INDIRECT("1:10"))}

and will return ( in pressing F9 ) :

>>
=40119000500+{{1;2;3;4;5;6;7;8;9;10}

>>

{40119000501;40119000502;40119000503;40119000504;40119000505;40119000506;40119000507;40119000508;40119000509;40119000510}

Regards
Hi Bosco,
Appreciate your reply. Actually I am looking for a dynamic formula. My invoice no contains 10 characters; 4119000500. I have a list of invoices number whereby a cell could have a single invoice number and also a range of invoice numbers like “4119000200 - 4119000200”; which contains 24 characters.

I want to use a formula where if(len(A1) =10,A1,if len(A1) > 10, provide the list of invoice numbers <I don’t know what would be the suitable formula for this application>

#### bosco_yip

##### Excel Ninja
Hi Bosco,
Appreciate your reply. Actually I am looking for a dynamic formula. My invoice no contains 10 characters; 4119000500. I have a list of invoices number whereby a cell could have a single invoice number and also a range of invoice numbers like “4119000200 - 4119000200”; which contains 24 characters.

I want to use a formula where if(len(A1) =10,A1,if len(A1) > 10, provide the list of invoice numbers <I don’t know what would be the suitable formula for this application>
To create a invoice number list

In B2, formula copied across and down :

=IF((LEN(\$A2)=10)*(SUM(\$A2:A2)=\$A2),\$A2,IF(COLUMN(A1)<=-IMREAL(IMDIV(\$A2&"i","1+i"))*2+1,IMREAL(\$A2&"i")+COLUMN(A1)-1,"")) Regards
Bosco

#### Attachments

• 11.8 KB Views: 16

#### herofox

##### Active Member
bosco_yip
Always creative and amazing

#### deciog

##### Active Member
Bosco, wow, wow, wow, fantastic, you are the guy

Decio

#### Peter Bartholomew

##### Well-Known Member
Amazingly creative use of complex arithmetic to process the invoice numbers! Congratulations, Bosco! I am not sure what proportion of Excel users are familiar with the concept of imaginary numbers, much less could use the rules of complex arithmetic to process the data.

Since I am working with dynamic arrays, I chose to use the SEQUENCE function to generate invoice numbers.
The spill error (with red CF) is deliberate to demonstrate where the formula goes and that the remainder of the row does not contain formulas. #### Attachments

• 15.1 KB Views: 3
• deciog

#### deciog

##### Active Member
Peter

I also liked, not to give error check what I did, I hope I helped

Hugs

Decio

#### Attachments

• 12.9 KB Views: 3

#### Peter Bartholomew

##### Well-Known Member
Decio
Thank you, looks good to me. I think I may well have misunderstood the initial requirement as 'generate 10 invoice numbers unless a different number is explicitly requested'. I notice that in cell C3 you had a relative reference to B3; that is simply the invoice number, giving the formula
= IF( LEN(invoiceNumber)>10, SEQUENCE(1,count,start), invoiceNumber )
I do not expect to have errors (a #SPILL! error cannot be hidden and, anyway, would need to be corrected) so I removed that test.

I had also played with the complex arithmetic that gives the count of invoices required and obtained a slightly different expression for count
= 1 + IMAGINARY( IMPRODUCT( invoiceNumber & "j", "-1-j" ) )

It is possible to read over IMREAL without really seeing it but the function IMAGINARY jumps out of the page!
Peter

#### Attachments

• 13.6 KB Views: 2

#### liabilityquek

##### Member
To create a invoice number list

In B2, formula copied across and down :

=IF((LEN(\$A2)=10)*(SUM(\$A2:A2)=\$A2),\$A2,IF(COLUMN(A1)<=-IMREAL(IMDIV(\$A2&"i","1+i"))*2+1,IMREAL(\$A2&"i")+COLUMN(A1)-1,""))

View attachment 65403

Regards
Bosco
Hi Bosco, tried your formula and amended column to row at the last part of the formula, did not display the invoice values I wanted. Would you be able to take a look at my attachment and what is wrong?

#### Attachments

• 168.7 KB Views: 5

#### bosco_yip

##### Excel Ninja
Hi Bosco, tried your formula and amended column to row at the last part of the formula, did not display the invoice values I wanted. Would you be able to take a look at my attachment and what is wrong?
New question, new post.