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

[liabilityquek]

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.

please advice.

 

[Hany ali]

pls Upload the File

 

[liabilityquek]

As per attached

 

[bosco_yip]

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

 

[liabilityquek]

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]

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

 

[herofox]

bosco_yip
Always creative and amazing
An excellent answer

 

[deciog]

Bosco, wow, wow, wow, fantastic, you are the guy

Decio

 

[Peter Bartholomew]

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.

 

[deciog]

Peter

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

Hugs

Decio

 

[Peter Bartholomew]

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

 

[liabilityquek]

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?

 

[bosco_yip]

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.

Please open a new thread for your new question.

Regards