google sheets sucks

[finndo77]

I don't really understand what they are saying, but according to the help docs and google community "helpers" referencing a cell from another cell doesn't work after modifying the location of the referenced cell. this makes no sense to me.

example from the worth file I am using: what you see: _	A	B
8	acacialog	14.00
9	acaciaplanks	3.50

what is actually there: _	A	B
8	acacialog	14.00
9	acaciaplanks	=B8/4

according to Google, if I insert a row between 1 and 7, this breaks the formula, because B8 is no longer the same cell. this makes no sense to me, I've been using MS Excel since around 1992, and google sheets, since they came out with it, and I've never seen this behavior before in either. While I have used Open Office writer off and on for the last 10-11 years also, I can tell you I've never run into this there either, but I can't tell you if I've ever tried to do this in Open Office.

[finndo77]

because of this I cannot sort by highest value and take a quick look at anything that appears out of place.

[finndo77]

this also means that I possibly messed up every formula on a line below anything I've removed in #19

[finndo77]

cake was:

line	name	value
cake	5222.50

line	name	formula
cake	=((B5153)+(B8402)+(B273)+(B8823)-(B1253))*2.5

now fixed line	name	value

cake -209.38

line	name	formula
cake	=((B5143)+(B8392)+(B273)+(B8823)-(B1253))*2.5

the "moved cells" were milkbucket became minecart, so the price was a lot higher than it should have been... but I noticed the value was negative and looking further, milkbucket is using the value of bubblecoralfan and not bucket... so fixed that also... making the cake value = 40. note that the cake's math includes the return of the buckets, so players cannot abuse pricing by making cakes over and over.

I am going to guess everything using a bucket will be off as well.

[finndo77]

a quick check of tropical fish bucket, water bucket, and lava bucket returned the correct formulas, so not sure what happened to the milk buckets...

[finndo77]

possible solution found, I have exported the google sheet to Libreoffice Calc, and the formulas don't f' up when I make changes to the doc. moving forwards, I will be using libreoffice for this. I may not be able to maintain the google sheet tab with values (formulas) any more, if I cannot, I will upload the open office file to this repo.

[finndo77]

a quick test and replacing the google sheets data with data from libre worked perfectly. I'll keep that file up to date with each pull request.

[finndo77]

for anyone that finds this somehow on a google search in the future, LibreOffice Calc has the same issue as Goggle Docs as of an update in 2015; however several months later they added a settings option that allows you to sort without messing everything up.

references: LibreOffice forum thread Solution: Tools->Options->Calc->General->“Update Refs when Sorting” is the place to go in current versions to make it work how you want.

The current version as of this post has it unchecked by default. I cannot find any similar setting in Google Sheets, looks like I am moving my entire life back to LibreOffice, which I left for convenience of sheets years ago, but now that I have my own NextCloud setup, Google is fast on it's way out of my life.