VLOOKUP failure

Questions and answers about FreeOffice PlanMaker 2018 for Windows
Post Reply
Woody44
Posts: 137
Joined: Wed May 22, 2019 11:56 pm

VLOOKUP failure

Post by Woody44 » Tue Jun 16, 2020 8:32 am

I have a spreadsheet that was created in Excel 2003. Several cells and formulae rely on the VLOOKUP function. One of the cells is tied to a warning that is supposed to pop up only if the result in that cell exceeds a pressure that is entered into another cell by VLOOKUP. It works correctly in Excel 2003 -- when the new pressure doesn't exceed the values in the lookup table, the warning box remains blank. In PlanMaker 2018 the warning pops up even though the pressure doesn't exceed the values in the lookup table.

I deleted my beta copy of SoftMaker PlanMaker 2021 when the beta timed out, but this issue my be present it that, as well as in the Free Office 2018 version. How can I send you the file so you can see what went wrong?

martin-k
SoftMaker Team
SoftMaker Team
Posts: 2876
Joined: Fri Nov 21, 2003 4:57 pm
Location: Nürnberg, Germany
Contact:

Re: VLOOKUP failure

Post by martin-k » Tue Jun 16, 2020 8:38 am

Please e-mail it to forum (at) softmaker.de and reference this thread.
Martin Kotulla
SoftMaker Software GmbH

Woody44
Posts: 137
Joined: Wed May 22, 2019 11:56 pm

Re: VLOOKUP failure

Post by Woody44 » Tue Jun 16, 2020 9:16 am

Martin -

Thank you for the quick response. It turns out the problem I thought I had encountered was a problem of working when I'm tired. The spreadsheet has a drop-down box to load one of the cells from a lookup table. Everything that happens in the sheet depends on the selection from this drop-down box ... and I clicked the wrong selection and then didn't notice the error as I entered data in the other variable cells. Needless to say, I got an unexpected result. Once I spotted that and corrected the selection, the error went away.

I've sent the spreadsheet to a friend for testing. Once I have his comments back I'll do more testing on this end.

One thing I noticed isn't a failure -- exactly -- but it's a behavior that's potentially confusing. I have six cells that accept user input (one of which is the drop-down box). All other cells, including those whose entries are pulled from a lookup table or calculated, are protected. In Excel, if I mouse over a protected cell and try to select it ... I can't do it. In PlanMaker, it IS possible to put the cursor in a protected cell. I can't type in it, but I can put the cursor in it. That doesn't seem right.

sodaspop
Posts: 4
Joined: Thu Jan 14, 2021 7:01 pm

Re: VLOOKUP failure - mystery

Post by sodaspop » Thu Jan 14, 2021 7:22 pm

Been using XLS files for years with MS Excel under Windows 98 and Windows 7. One file, "ledger," has several worksheets and Vlookup tables used by another file, "invoice." "Invoice" was usually opened and was able to access the tables OK without "Ledger" being opened.
New computer, Windows 10, new version for Planmaker: when "Invoice" is opened and accesses "Ledger," not opened, the Vlookup works fine as it had but only finds data in the first half of the Vlookup (row) range. The table in "Ledger" goes from A2:C400. The Vlookup works fine up to row 201, then any lookup in the table past that row returns zeroes. The workaround is to have "Ledger" opened in the background before "Invoice" is opened, and then "Invoice" successfully fetches data in rows past 201. Why would Vlookup bail after half of the table is accessed when the file with the tables is closed and work OK only after the file with the tables is opened first.
Lookup column is properly numbered in sequence, nothing unusual

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1415
Joined: Wed Mar 11, 2020 5:31 pm

Re: VLOOKUP failure

Post by SuperTech » Thu Jan 21, 2021 11:30 am

As Martin asked please share the file so that we can do further investigation on this problem.

sodaspop
Posts: 4
Joined: Thu Jan 14, 2021 7:01 pm

Re: VLOOKUP failure

Post by sodaspop » Thu Jan 21, 2021 3:30 pm

I will. First will test files on another PC with PM. If problem is same, I'll replace personal data with generic info and test again, then send to you

sodaspop
Posts: 4
Joined: Thu Jan 14, 2021 7:01 pm

Re: VLOOKUP failure

Post by sodaspop » Fri Jan 22, 2021 3:50 pm

Here is a review of the problem and one of the 2 needed files. It won't take the Pat Ledger file as it says "file too large."
Both files are at the Dropbox link below, but I would like to keep that private if possible.

The Vlookup works OK in Excel but not PM. With both files in the same directory, open Invoice7, enabling links (to Pat Ledger).
(Do not open Pat Ledger)
Invoice7 File is in a Protected mode.
Click the Invoice # field and put a random number there, Tab to Account # field
put in a number between 100 and 301 and Name, address area will show data fetched by the Vlookup from Pat Ledger. (Date is same for all entries).

In the Account # field, put a number between 302 and 377 and zeroes will show up. (There is customer data within that range of the Vlookup table)

Close Invoice, open Pat Ledger and leave open in background, then reopen Invoice.
Repeat the process. The difference is that data will show up properly in the 302-377 range where it had been zeroes when the Invoice file was opened on its own.
Put in an account # of 380, for example, and now zeroes should show up. (there is no data between Cust. number 377 and 400)
The failure to display data past #301 is perhaps, only coincidentally, half way through the Vlookup range.
When done, exit without saving.

https://www.dropbox.com/sh/ycyvua8mbe36 ... Nte2a?dl=0
Attachments
Invoice7.xls
(76.5 KiB) Downloaded 14 times

SuperTech
SoftMaker Team
SoftMaker Team
Posts: 1415
Joined: Wed Mar 11, 2020 5:31 pm

Re: VLOOKUP failure

Post by SuperTech » Sun Jan 31, 2021 12:59 am


Post Reply

Return to “FreeOffice PlanMaker 2018 for Windows”