Hi, folks. Apologies if I’m breaking protocol here. This is my first time posting, and I’m only posting as an absolute last resort. I’ve tried troubleshooting this problem for several hours every day for nearly a week now, and I’m at my wits’ end. If anyone could put an end to my suffering, I’d be extremely grateful.
Now, to preface all of this, I’m a rookie when it comes spreadsheets and databases. I know enough to do very basic things, but not much else. I recently started playing a game of Factorio, and I wanted to use LibreOffice Calc to help me calculate a few things so I could make better decisions while playing. I started by creating a list of the various machines in the game and putting them onto a Calc sheet along with all of their associated characteristics such as their crafting speed, how much energy they consume, etc. The idea was to put all of this data onto a single sheet, a so called “data sheet”, and then perform whatever calculations I want to do on other sheets by referencing data from the data sheet with VLOOKUP.
Now, this was all fine and dandy. I successfully managed to get this far without any major complications. However, something I realized about VLOOKUP is that the range of cells it looks through is static. The data on my data sheet, on the other hand, is far from complete. My hope was that, as I progress through the game and unlock new machines, I could append those machines to the end of my data sheet without breaking any of the VLOOKUP functions I’ve already written. Unfortunately, that does not seem to be the case as appending new machines to the end of my data sheet does not seem to “update” the range on any of my existing VLOOKUP functions.
At a loss, I began to look for possible solutions, and found the following:
-
Manually update the range on all of my VLOOKUP functions whenever I append new entries onto my data sheet. This is what I am trying to avoid. Manually updating every VLOOKUP function would not only be incredibly tedious, but it would require me to remember where all of those VLOOKUP functions are. Not to mention, I would have to remember to do this each time I add or remove entries from the data sheet.
-
Insert new rows into the data sheet whenever I want to add new entries instead of appending them to the end of the sheet. This is something I’m also trying to avoid. This solution will “update” my VLOOKUP functions so that they search through the proper range, but it requires me to always remember to insert new entries. This is not something I want to have to remember. Not to mention, if this were something less trivial and I needed to pass it along to somebody else, then they would also need to know to insert every new entry instead of appending them, and there’s no guarantee that they would realize that.
-
Give my VLOOKUP functions a gratuitous range so that they won’t need to be updated in the future. This seems like a hack to me. While I have no doubt that it will work, it doesn’t really solve the problem of Calc not knowing what range my VLOOKUP functions should actually have. It might also make my calculation times slower? Not to mention, in the incredibly rare circumstance that I somehow don’t make this range large enough and my data sheet begins to exceed it, then my VLOOKUP functions would begin to invisibly break, and I wouldn’t likely know why.
As if by magic, I eventually stumbled across this article (https://exceloffthegrid.com/automatically-expand-vlookup-data-range/) which seemingly understands my exact concerns perfectly, but it was tragically written for Excel users, and I was unable to extrapolate anything from it to use in Calc. I would later find some advice (I forget where) that suggested I use an actual database instead of trying to use Calc as a database since that is essentially what I am doing, so I downloaded whatever Java runtime environment was necessary to use LibreOffice Base and thought I’d give that a try.
At this point, I succeeded in creating a new database which contains a table that has exactly the same values that my data sheet has. I have also linked the database to my Calc worksheet. From here, I’m not sure how to get Calc to reference any of the data from my Base table though. I discovered a way to create a pivot table in Calc using data pulled from my Base table. This table will allegedly update when I click the refresh option on it, but I haven’t tested that feature yet as I’m not even sure if I can assign a pivot table to the entire range of a VLOOKUP function. Also, pivot tables terrify me, and I would prefer not to use one if at all possible. For reasons I do not understand, every pivot table I’ve ever created has an additional blank column labeled (empty), and I’ve spent almost an equally ridiculous amount of time trying to figure out how to get rid of it, but to no avail.
In any case, that is essentially what my journey has been up until this point. If you have any suggestions at all for how I might tackle this problem, please let me know. I would prefer not to use a database if possible as it seems to me like it ought to be gross overkill for what I’m trying to accomplish, but I’m willing to use one if necessary. I’m also not married to the VLOOKUP function either, and if there’s some other search function that I don’t know about which could easily solve my problem, I’d love to know about it. Thanks!
