Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 24 Sep 2017 11:32:40 +0200Calc hangs on VLOOKUP, files larger then expectedhttps://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb,
What must do?
can help me ?[C:\fakepath\LOupload.xls](/upfiles/15062373165037548.xls)Wed, 20 Sep 2017 07:29:35 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/Comment by Steve R. for <p>I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb,
What must do?
can help me ?<a href="/upfiles/15062373165037548.xls">C:\fakepath\LOupload.xls</a></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=131697#post-id-131697When the sheet turns gray, that implies that the computer is busy (stressed-out). You didn't indicate if this is a new problem. If a new problem, that could be a result of a bad formula that is hogging CPU time. There could be other reasons too.Thu, 21 Sep 2017 04:28:16 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=131697#post-id-131697Comment by Kruno for <p>I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb,
What must do?
can help me ?<a href="/upfiles/15062373165037548.xls">C:\fakepath\LOupload.xls</a></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132082#post-id-132082I reworded title and retagged question as it has better chance now to attract attention of someone who can actually provide solution.Fri, 22 Sep 2017 12:42:53 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132082#post-id-132082Comment by dimzev for <p>I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb,
What must do?
can help me ?<a href="/upfiles/15062373165037548.xls">C:\fakepath\LOupload.xls</a></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132401#post-id-132401OK , my english is not very well, thank youSat, 23 Sep 2017 18:26:06 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132401#post-id-132401Answer by karolus for <p>I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb,
What must do?
can help me ?<a href="/upfiles/15062373165037548.xls">C:\fakepath\LOupload.xls</a></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?answer=132094#post-id-132094hallo
Propably you can significantly speed up the recalculation of VLOOKUP if you sort the Search-Array by the First Column, and use VLOOKUP **without 4th Parameter** or **4th Parameter 1**Fri, 22 Sep 2017 13:38:53 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?answer=132094#post-id-132094Comment by dimzev for <p>hallo </p>
<p>Propably you can significantly speed up the recalculation of VLOOKUP if you sort the Search-Array by the First Column, and use VLOOKUP <strong>without 4th Parameter</strong> or <strong>4th Parameter 1</strong></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132302#post-id-132302i dont understand..
if serch is only First column how i take the result from second column ?Sat, 23 Sep 2017 11:16:48 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132302#post-id-132302Comment by Kruno for <p>hallo </p>
<p>Propably you can significantly speed up the recalculation of VLOOKUP if you sort the Search-Array by the First Column, and use VLOOKUP <strong>without 4th Parameter</strong> or <strong>4th Parameter 1</strong></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132486#post-id-132486Think he wants you to sort your whole table by first column and then you use VLOOKUP without 4th paramater (or use '1' as 4th paramater). Make a backup copy of your spreadsheet, try this and see if you gain some speed.Sun, 24 Sep 2017 09:18:16 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132486#post-id-132486Answer by m.a.riosv for <p>I use spreadsheets with about 30000 rows and check with the Function VLOOKUP sheets of calculation with about 8000 rows and many times sticks- collapses (turns gray) and I have a big problem. the size of each file is from 10Μb to 18Mb,
What must do?
can help me ?<a href="/upfiles/15062373165037548.xls">C:\fakepath\LOupload.xls</a></p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?answer=132241#post-id-132241There was an slowness issue with some functions like VLOOKUP searching in a range,if the option `Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells` it's no marked.
Fri, 22 Sep 2017 19:18:24 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?answer=132241#post-id-132241Comment by dimzev for <p>There was an slowness issue with some functions like VLOOKUP searching in a range,if the option <code>Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells</code> it's no marked.</p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132300#post-id-132300in my case: >> Search criteria = and <> must apply to whole cells<< it is markedSat, 23 Sep 2017 11:13:41 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132300#post-id-132300Comment by m.a.riosv for <p>There was an slowness issue with some functions like VLOOKUP searching in a range,if the option <code>Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells</code> it's no marked.</p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132320#post-id-132320Then please attach a sample file so some one can take a look for a better way to do it.Sat, 23 Sep 2017 14:44:12 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132320#post-id-132320Comment by dimzev for <p>There was an slowness issue with some functions like VLOOKUP searching in a range,if the option <code>Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells</code> it's no marked.</p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132398#post-id-132398can you help me how can attach a sample fille please?Sat, 23 Sep 2017 17:29:05 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132398#post-id-132398Comment by dimzev for <p>There was an slowness issue with some functions like VLOOKUP searching in a range,if the option <code>Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells</code> it's no marked.</p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132485#post-id-132485i upload a simple fille to see my settings ,
i try to upload a large fille and i cantSun, 24 Sep 2017 09:16:32 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132485#post-id-132485Comment by m.a.riosv for <p>There was an slowness issue with some functions like VLOOKUP searching in a range,if the option <code>Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells</code> it's no marked.</p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132442#post-id-132442Edit your question and add the file to share.Sun, 24 Sep 2017 01:30:49 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132442#post-id-132442Comment by m.a.riosv for <p>There was an slowness issue with some functions like VLOOKUP searching in a range,if the option <code>Menu/Tools/Options/LibreOffice calc/Calculation - Search criteria = and <> must apply to whole cells</code> it's no marked.</p>
https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132499#post-id-132499No VLOOKUPs in your file, but maybe the issue is with STYLE function it is a volatile function, so with any change, any where in the file, they are calculated.Sun, 24 Sep 2017 11:32:40 +0200https://ask.libreoffice.org/en/question/131448/calc-hangs-on-vlookup-files-larger-then-expected/?comment=132499#post-id-132499