• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

liabilityquek

New 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.

please advice.
 

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:

liabilityquek

New 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,""))

65403

Regards
Bosco
 

Attachments

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.

65439
 

Attachments

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

liabilityquek

New 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

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.

Please open a new thread for your new question.

Regards
 
Top