Guides

@risalex Please feel free to shoot me any specific questions you have via DM. :) The data can be crunched a lot of different ways and I've found the best way for me to figure out what I'd like to try is to say "so here's my goal" and then work backwards and find out what data I need and what formulas let me accomplish that goal efficiently.

The basic gist of this lair and datasheet is that the lair sheet refers to the datasheet through a hidden tab that imports all the data from the "home" datasheet. From there, the lair sheet checks any inputted data in cells against "allowed" data on the datasheet (data validation). It applies conditional formatting to color the appropriate cells which was something manually done by Hex back in the day that I've just been able to copy over.

For things such as the gene project sheet, the gene sheet will look up a particular gene with the VLOOKUP formula and then return an adjacent cell with the value of that particular gene, then do a little math and some more conditional formatting to make it look pretty and give the user an idea of how much to budget for a project.

The best way to learn from the sheet is to just look at the formulae that are used and "unhide" all the hidden tabs and columns on your own version so you can get an idea of the parsing going on behind the scenes. From there, you get a pretty good idea of what cells are referencing other cells and what calculations are being done to make the magic happen.

ChibiFluffy's sheet uses exactly the same principles and you can check their thread and go back to Hex's original post in this one to get a good idea of ways this can be applied.

An example of what I'm working on using similar ideas is my current pet project, my own personal Baldwin spreadsheet. I'm working on finding a way I can track brewing and recipes. You can see I have a bunch of tabs at the bottom where I've been playing with the data I have for the recipes and trying to figure out ways I can mess with it... but I've reached a bit of a standstill since I think what I need is into the level of using a proper programming language instead of just google sheets formulae and that's beyond me at this point... But it's fun to work on and I actually apply it a lot in my irl job, so it's proven useful too!

@Hideki I'm gonna send you a DM to troubleshoot. :)

@magebee thank you so much for all the info! I'm going to be having a lot of fun playing around with how I can visualize data and learning more about Sheets in general.

nice to see this thread still going :) big thanks to @magebee!

(btw, do you know about XLOOKUP? it's basically VLOOKUP's big brother on steroids and i love it)

[quote name="Hexephre" date="2023-11-22 12:26:16" ] nice to see this thread still going :) big thanks to @magebee! (btw, do you know about XLOOKUP? it's basically VLOOKUP's big brother on steroids and i love it) [/quote] XLOOKUP my beloved..... <333 With my Baldwin sheet, my big problem is what I REALLY want is like a full-blown mobile app and I just don't have the patience to figure out how to make it for myself lol. Thank you for kicking this off, @Hexephre. On a very personal level, I have had a ton of joy learning from your work and a lot of fun poking around and keeping things updated. Let me know if you ever want me to butt out of your thread (or if you want to take the reins back haha), but I'm very grateful for the opportunity.

@magebee oh an app would be great! that's far beyond my own capabilities though haha. here's hoping someday you fnd a collaborator to make all your dreams come true :)

these spreadsheets have given me lots of joy in the past as well, but i'm not involved enough with FR these days to keep them going myself. i'm ecstatic you've picked it up and are finding joy in it as well! i've always felt a bit guilty leaving these kinds of projects behind, so it's great to see like-minded, enthusiastic people carrying on the torch. if anything, i wish i could hand you editing rights to the front posts!

@Hexephre you're back! i've been lovingly maintaining your hibden sheet for a while now, and i thought you'd like to know that it's still around!

@Pachirisu ooh, thanks for letting me know and thank you for keeping it going! it's so nice to see that people are still finding these useful ^^