Page 1 of 1

[Solved] VLOOKUP() on multiple files

Posted: Sat Oct 30, 2010 10:18 am
by lavi0007
Hi, This is my first post in this forum. We've jus deployed Openoffice in our organization nearly (35 computers). I'm the tech head giving technical advice to the organization. Our organization relies solely on Excel for all all kinds of work as we're into payroll processing and provident fund processing and accountancy , so the size of excel is extra-ordinarily use. The plan to some extent has back fired as users are facing plenty of issues:-

1. It takes ages to open/save excel files.
2. Vlookup is the lifeline of our organization. We're not able to apply vlookup across multiple files.(This one is a major prb)
3. When we try to copy data from cell having formula in place across files using "+" operator, it copies file path rather than the value but across the sheets it works perfectly fine.

PS. For openoffice being slow prob i tried that tweak of modifyin the memory settings and disabling JRE, but its not working

Re: vlookup on multiple files (Urgent)

Posted: Sat Oct 30, 2010 10:32 am
by Hagar Delest
Hi and welcome to the forum!
lavi0007 wrote:1. It takes ages to open/save excel files.
I'm not sure it's safe to still use .xls format with OOo. Until recently, the specification was not available. The import/export filters have been reverse engineered so you may face some problems like time needed to convert the file each time you open and save. Have you tried if saving as .ods improves the situation?

For the other questions, I let the Calc gurus answer.

Re: vlookup on multiple files (Urgent)

Posted: Sat Oct 30, 2010 1:05 pm
by lavi0007
found the solution for vlookup on multiple files, first had to save the files first. Saving in ods is of no relief as files are huge even opening of the files is taking very long. Is there any way to sort it out. Apart from that when we copy the data from a cell of a different file that has a formula applied to a different file, it just copies the path rather than the data or the formula.

Re: vlookup on multiple files (Urgent)

Posted: Sat Oct 30, 2010 2:56 pm
by Villeroy
Nobody can reproduce your issue. Copy and paste some formula and describe what you are doing. There are 5 different ways to link sheet cells across files.
[Tutorial] External Links In Calc

Re: vlookup on multiple files

Posted: Sun Oct 31, 2010 12:56 pm
by lavi0007
Hi Villeroy,

1. The issue still persists about speeding up the spreadsheet as ours quite big. I've done the following settings for the openoffice I've disabled java runtime environment. Reduced number of steps to 20, Increased graphic cache to 128, Memory per object to 20, Number of objects to 20.
2. For the vlookup i found what the problem was. Well the users were copying the data to a file that they just created and did not save it. When they applied the vlookup formula they were able to pick the data from the same file but when tried to lift data from across the files it did not work. So inorder for vlookup to work first save all the files and then play with it.
3. I guess this problem was unclear by you. I'll try to explain. Create two spreadsheets named 1.xls and 2.xls, now on spreadsheet 1.xls place two values 10, 20 and in the third cell put the formula =sum(Cell1;Cell2), you'll the result in the third cell. Now in spreadsheet 2.xls, in cell1 put "+"operator and select cell1 from spreadsheet 1.xls. The "+" operator lifts the value from Cell1 and places it in cell1 of spreadsheet 2.xls. This works fine. But if you try to do the same thing with the cell3 (The one having sum formula), it's just lifting the file path, rather than the value in cell3.
4 -> New one -> I've been working on openoffice to try to sort some issues on my own so that i can help others in the organization. Well what i'm doing is i'm creating two spreadsheets in odf or xls (any format). i'm putting some random value in sheet 1 of file 1 and saved it. Now in file 2 when i'm putting the "+" operator and trying to lift the value from file 1 it shows 0. Now i close both the sheets and re-open them, it then changes the value to that of file1 in file2 as it should had been earlier but that was not happening.

But overall openoffice is really nice. I hope i was able to create the problem well enough so that everyone can reproduce it now.

Thanks in advance

Re: vlookup on multiple files

Posted: Sun Oct 31, 2010 12:59 pm
by lavi0007
The last problem, about picking the values from different files, whenever i'm using the "+" operator and selecting the cell in another file, it shows 0. When i close the file and then re-open it, its showing the correct value.

Re: vlookup on multiple files

Posted: Sun Oct 31, 2010 6:33 pm
by Charlie Young
lavi0007 wrote:The last problem, about picking the values from different files, whenever i'm using the "+" operator and selecting the cell in another file, it shows 0. When i close the file and then re-open it, its showing the correct value.
You might try going to Edit > Links > Update. I have found that for some reason it is sometimes necessary to coax the update by hitting the update button repeatedly, but there should be ways to force the issue by macros if necessary.

Re: vlookup on multiple files

Posted: Sun Oct 31, 2010 7:36 pm
by lavi0007
Thanks charlie i'll just try it.. but don't you think for less technical person like my other office staff, its kind of complicated to explain them why they have to do it. Coz they're totally anti against the idea of using openoffice. Well its just a personal question, hope annoys no one. But is there any other way to solve this.

Re: vlookup on multiple files

Posted: Sun Oct 31, 2010 8:08 pm
by lavi0007
Just checked everytime i'm making the changes to a sheet or adding new values to the sheet1 of file 1 and then i try to pick the value from the sheet 1 of file 1 to sheet 1 of file 2. If i jus simply use "+" or "=" it'll show me a "0" for the value, the way around is if i'm adding new formula to sheet 1 of file1, then i have to save file 1 first, then go to Menu > Edit > Links update as Charlie said. Is that admissible or there's some other way around. Please let me know if its the only way, i'll explain the same to the users. Thanks Charlie, Villeroy and Hager, thanks a ton.

Re: vlookup on multiple files

Posted: Sun Oct 31, 2010 10:33 pm
by Villeroy
lavi0007 wrote:but don't you think for less technical person like my other office staff, its kind of complicated to explain them why they have to do it.
No, it is easy to explain: Snapshots of the source file data are embedded in the target file. You can carry the file on USB or upload the target file and it will keep on working without the source files unless you try to refresh.
The manual update will try to read the source files as they are stored on the disk. It will re-import the data cache from the stored source file rather than the dirty version in memory.

If you need spreadsheet links that work differently, you may try the DDE function. It loads the source file invisibly at first and it can update automatically when you make the source file visible and edit.

Please mind: A spreadsheet is not a database. For what you obviously try to do and for many reasons, the spreadsheet is the wrong tool. A database is designed to work with many millions of rows in hundreds of interrelated tables without problems. No technical person would ever build up a database structure in a spreadsheet (well, may be some tiny collection of small lists without too much importance). For less technical persons a well crafted database setup is far easier to work with and less error prone.