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

full value to be extracted.

S

shibulal

Member
I have a sheet which extract the desired result from an another sheet with a formula like : {IF(COUNTIF(Ledger!B:B,Chk!$M$9)<ROWS($K$13:K13),"",INDEX(Ledger!F:F,SMALL(IF(Ledger!$B$2:$B$70284=Chk!$M$9,ROW(Ledger!$B$2:$B$70284)),ROW(Ledger!B1))))}.
But it is not fetching the value beyond rows 65000+. What may the reason ?. I changed the file extensions from XLSX and xlsm. But it didn't work. Please help.
 
I guess you should use "Save As" command then under "Save As Type" save it with xlsx. I think it may help you.

In earlier versions of excel there are some limitations in comparison to newer version.
 
Last edited:
Is it because of cross worksheet reference of array formula which limits to 64000 ?.

If so, is there any solution to extract all the values beyond row 64000 ?
 
@shibulal

Have you read about this restriction somewhere? Can you share the link, as I had not crossed any such thing. May be you can share the sample file.

Regards,
 
Hi Shibu ,

I don't think there is any such restriction , except for the file type restriction.

I have tried the following formula :

=IF(COUNTIF(Ledger!B:B,Chk!$M$9)<ROWS($K$13:K13),"",INDEX(Ledger!F:F,SMALL(IF(Ledger!$B:$B=Chk!$M$9,ROW(Ledger!$B:$B)),ROW(Ledger!B1))))

in a .xlsx file , with data on row 1040000 , with a correct result.

Narayan
 
Dear Somendra,

I checked it with other machines having different memories. (a lil' variations).
All the machines has given same result. From my curiocity, I have filled first 5 rows (A1 to D5) and then filled last 5 rows (A100000 to D100000). Here the index formula worked.Extracted the values even from ROW A100000. Then I filled the data from A1 to D100000 (without any blank rows), the indexing stops at ROW 64000.

Thanks,
Shibu
 
@ somendra,

for testing purpose, I can say yes.
When I work with a genuine report file, I will have different records in each cell.
 
With this the only possibility is that the no. of rows on your Ledger Sheet is 65536.

Can you check the last row number on ledger sheet?

Regards,
 
Hi Shibu ,

The INDEX function is not concerned with whether there are blank rows in between.

The only possibility is if you are using a .xls file , where the number of rows is less than the value you are trying to use viz. 70284.

Narayan
 
Hi Shibu ,

If you cannot upload your file , we are just wasting time.

Try using only the INDEX function , and post back what you get :

=INDEX(Ledger!F:F,SMALL(IF(Ledger!$B$2:$B$70284=Chk!$M$9,ROW(Ledger!$B$2:$B$70284)),ROW(Ledger!B1)))

entered as an array formula , using CTRL SHIFT ENTER.

Secondly , are you saying that this formula is the problem , or is it that this formula becomes problematic when you copy it downwards ?

Narayan
 
You must log in or register to reply here.
Back
Top