PDA

View Full Version : Spreadsheet Help Needed!



Bjarkmundur
2019-09-25, 03:06 PM
I thought about consolidating all my random tables into a spreadsheet.



Random Table
Column
Dice
Dice Result


Crit Table
Melee Weapon
1d100
56


This is a dropdown^
This is a dropdown^
This is fetched information
based on the two drop-
down menus chosen
This what we type in based on the physical dice roll



Then there's another cell with the effect.


We love random tables, and this would significantly reduce the time spent flipping through papers and finding the right table. The problem is that I don't know how to 'fetch' information based on conditions.

With this sheet we can simply roll the dice, and have the result appear in just a couple of clicks.

Can anyone help me code this?

EDIT: I started with my limited knowledge over here (https://docs.google.com/spreadsheets/d/1NfxAz5ipRDEIQwfs1JWnRwxZ6-vRVa9SveIrcwBPu7U/edit?usp=sharing).
VLOOKUP seems to be helping, but it can only use one input. Anyone?

Quizatzhaderac
2019-09-26, 11:29 AM
So answering your specific question about lookup with two inputs first.

In your lookup table, create the third column called "key", create the value by concatenating the values of the "table" and "column" columns. Do the vlookup by the concatenation of the values in the drop-downs.

More generally:

It seems to you trying to actually lookup tables and then do a second lookup on the specific table.

Do these tables all have the same set of columns? Because if not you'll need to lookup the column options based on the tables.

Bjarkmundur
2019-09-26, 05:13 PM
Thank you kind stranger!

I'm trying to decide if I want to recompile all the tables I'm done writing up into separate tables in reverse order. I'll probably do both MATCH and VLOOKUP versions. Since I'm already here I might as well learn both methods.
You'll see this document in my signature once it's done. I'll turn off editing for now. Thanks again :D

aimlessPolymath
2019-09-26, 05:17 PM
VLOOKUP does not have the ability to find "smallest number greater than equal to X" that MATCH does, so you'd need to write out your table fully, with one line for each possible die roll.

Bjarkmundur
2019-09-26, 05:48 PM
VLOOKUP does not have the ability to find "smallest number greater than equal to X" that MATCH does, so you'd need to write out your table fully, with one line for each possible die roll.

Yeah the MATCH function is a much cleaner way to do it ^^

playswithfire
2019-10-03, 03:39 PM
VLOOKUP does not have the ability to find "smallest number greater than equal to X" that MATCH does, so you'd need to write out your table fully, with one line for each possible die roll.

If you put the data in assorting order and set the fourth parameter of VLOOKUP(key, range, index, [sorted]) to true, it does the opposite, though, and finds the largest number less than or equal to X.

Your current version seems to be working well.