Excel help

[gamecat]

I'm toying with the idea of making a spreadsheet to randomly generate magic items from all of my splatbooks.

Sheet 2 will store all of my data, in two columns: A having a number for an item, B having the name and source of said item.

What formula would I punch in on sheet 1 to have it randomly retrieve rows from sheet 2?

 

[hafrogman]

Assuming a table set up on sheet2, like shown in the picture.

I used

=VLOOKUP(INT(10*RAND()+1),Sheet2!A1:B10,2)

This generates a random integer number 1-10, then compares it to the first column of the referenced table and returns the value in column 2.

So that's . . . (Index Value,Table Range, Return Column)

If you want a more percentile based system where 01-45 returns one item and 46-48 returns another, etc. etc. it will get more complicated but should use the same function.

 

[gamecat]

I'm gonna play with that.

Thank you very much! If you want me to design something for you, I'm a wizard with house rules and love a chance to something out in return.

 

[Rodrigo Istalindir]

You can also use the RANDBETWEEN function. A little neater for integer lookups.

Also, you'll have to have the Analysis toolpak add-in turned on to get the random functions to work. It's under Tools->Addins.

 

[maransreth]

You can also use the RANDBETWEEN function. A little neater for integer lookups.

Also, you'll have to have the Analysis toolpak add-in turned on to get the random functions to work. It's under Tools->Addins.

If this file were to be made available for others to use as well, then everyone else needs to have the Analysis Toolpak installed as well.

So while it is definitely a great option, it is best used on the machine that the person who created it is using.

Back to the original question, a problem you will encounter is if the number of items you have is getting too high. The Rand() method is useful for numbers between 1 and 100, but nothing higher. The Randbetween can be any series of numbers, but you need to have the Analysis Toolpak installed.

 

[NoviceGM]

Here are a bunch of usefull excel functions, if you toy with these and get familiar than you can make a lot of cool tools in excel for your game.


Lookup - Looks up a number or text from a list, can be done in rows or columns but the list must be sorted ascending. The thing being looked up MUST be on the list.

Vlookup - Looks up a value on a list and returns the closest result, only works well with numbers. So if the list has a 0,5, and 10, and you lookup 7, it will give you what ever was next to 5, because its closest. A lot of people use this in conjuction with a defined list, (insert, name, define).

MAX - takes the maximum number from a range, usefullin conjuction with lookup/vlookup..
use the lookup range to select your data and room for more, then when using randbetween, use MAXof the list for the high number, that way as you add new data, it will be automatically included in the lookups.

MIN - useful for limitations like max dex, works just like max.

IF - This is the best one you have, you can use it for just about everything. It makes a choice based on the criteria. If this is easy for you, try thr other logic functions.

ROUND - this one is useful

I made most of my GM sheets with these, If you have any questions let me know and I will help with what I can.