[Solved] Calc Functions seem to have a mind of their own...

[hoosyourdaddyo]

Hi guys,

I need help with a truly bizarre situation I'm having with Calc... So, I set up a bunch of data sheets, which have the vlookup formula used to encrypt and decrypt a simple substitution number-letter cryptograph.


For example =vlookup(a13;e13:f29;2;0). Assuming A13 is "A", this should then find A inside the range of e:13 to E:29, and marry it up to the corresponding number within f:13-F:29. This worked find, but then I did some shifting around on the massive spreadsheet, (over 100 tabs!), and now I've found that the formulas are now calling to the NEXT data sheet, and not the one they're in (I inserted a new tab in front of the data sheets, so I think this may be the issue). There's no evidence of it calling from the other data sheet (ala "sheet1" or "sheet2"), but just the vlookup exactly as I showed it before.

Does anyone know a way for me to fix this? I've tried literally typing the formulas back in, but that's buggy as hell too.

[Zizi64]

Please upload your small size ODF type sample file here. (max. 128 KiB)

[Villeroy]

[Tutorial] Absolute, relative and mixed references

[hoosyourdaddyo]

This is not an issue with relative values, there's no $ on any of the formulas...it seems to be a bug in the code, plain and simple.

Well, that's it, I've decided to give some more of my money to Bill Gates, because I've found OO to be too buggy to be reliable, and had to spend hours correcting problems caused by weird coding.

Stuff like having your formulas all defaulted to full relative at shut down- which could be a huge problem when working with a large number of data sheets, and report pulls that need to stay constant to their data source in a 1:1 matched pair. What I found is that OO Calc will default to setting the formulas to relative, which means I now have to go through and change those formulas I need to stay constant. It's a minor issue, but when you're working with hundreds of data set pairs, it's a major problem.

Another issue I've had is that to not print zero values, I have to go to each and every sheet I'm printing (or making into a PDF) and set each page individually to not print zero values. Once again, with a smaller document, nbd, but when you're working with a large one like I am, it's a huge issue. Finally, the fun I've been having watching formulas just start not working. This is the deal-breaker, and why I'm dumping OO. When I cannot rely on my data, and the calculations of the formulas contained in the spreadsheet, then I'm out of business.

I purchased an Office 365 license, and other than some version compatibility issues, which were easily solved, ALL of the above issues I had disappeared instantly.

Thanks for your efforts and good luck!

[keme]

This user story illustrates one important rule: When you use tools, you need to know the tool's strengths and weaknesses. More important perhaps: knowing when not to use a particular tool.

Sadly, we do not get sufficient information to identify and/or recreate the issue, only loose indications about what fuctions have been used, the magnitude of the work and what it is that isn't the issue With more specific info we might have reported back to the developers, or advised the user, as required.

The most important thing is that the user helped himself to a situation where his lookups are working again. Sometimes, somewhere in there, you have a bug, a MS-specific function usage, or some other mismatch which favors one product over the other. Good job, Daddy'o.

[Zizi64]

this should then find A inside the range of e:13 to E:29, and marry it up to the corresponding number within f:13-F:29

What is that irregular reference?

Please upload a sample file. I suppose it: your formulas have some typos or some similar "errors".

What file type are you using? Note: there is not (never was and never will be) 100% compatibility with the old, osolete, never standardized, foreign file formats (like the binary .xls). Always use the international standard ODF file formats. Or you can try the LibreOffice. It has a littlebit higher compatibility with the foreign file formats.

[Villeroy]

Any reference without sheet name refers absolutely or relatively to cells on the same same sheet. The one and only difference between Excel and Calc in this respect is that Calc knows relative sheet references and Excel does not.
Having Sheet1, Sheet2, Sheet3 etc. in that order, and Sheet1.A1 having a reference Sheet2.A1, this reference refers to the same cell on the next sheet whereas $Sheet2.A1 refers to the same cell on the absolute sheet named "Sheet2".

[Lupp]

When I urgently wanted to get sure about some aspects of the behaviour of Excel 2016 two years ago, I contacted a software retailer I knew. They couldn't help. They had de-listed the "product" and also thrown it off their own systems for its fault liability and for problems with the support for their customers. An explanation? "They (MS) want to push everything towards their 'services' and 'subscriptions'." Milk users, but don't let retailers live?

Of course it's nice to get paid every year for something done decades ago and just labelled green this year and pink the next year.

Why do their tricks work again and again?
They just work.
Dümmer geht's nicht. (Christian Ude about the recent changes in the software policy of the municipality of München)
A majority enacted ...

How to keep motivated to spend time contributing to forums on open/free software? Some experienced users may have spent time totaling to about an hour to "help" the questioner in this case. Did he spend half an hour trying to actually learn about spreadsheet fundamentals and about asking questions? Did he exercise himself in something beyond shallowness?
Surely he did. I'm just too dull.