Hi, first let me apologize for the length of this post. I tried to give specific details in hopes my instructions would be easy to follow in case someone was willing to help me with this. I can't figure out how to add spoiler tags on this forum, so I can't block this information off in an easier to navigate format. Sorry.
So to begin, I have a sheet that contains a long list of data that I have compiled. There are around 7000 rows (names) and 6 columns (data pertaining to those names).
I was wondering if someone would work with me to create a random generator using this data that works completely in an excel sheet. My ability to write formulas or VBA is limited, but I am great at following directions.
Here is a sample excel file that I created that contains sheets with a few rows of data for testing purposes (the values are in no way accurate):
http://www.rpglocker.com/DnD/Random Generator.xlsx
Here is how the generator would work:
I am not sure what the best approach would be as far as functionality and layout goes, but I went ahead and created 3 sheets: Generator, Items, and Charts.
Generator is the User screen.
Items is the database.
Charts is the operations.
I would like to be able to edit this generator on my own to include all 7000+ rows and also to add/adjust the data as needed in the future.
1. From the Generator sheet, you select from a dropdown menu a number from 1-30 which correlates to the "Level" in column B on the "Random Treasure Table" of the Charts sheet. It references those rows for the treasure being generated.
2. From the "Random Treasure Table" of the Charts sheet, it "rolls" a % between 1-100 (as in column C) and references the appropriate row. It generates a value from data in column D (Coins).
3. It then "rolls" again a % between 1-100 (as in column E) and references the appropriate row. It generates a value from data in column F (Goods).
3a. If the result is "gem", it references the group of rows on the Items sheet titled "Gems Generation". If the result is "art", it references the "Art Objects Generation" rows (also grouped).
3b. It then "rolls" a % between 1-100 (as in column B) on the gem or art table and references the appropriate row. Currently, the table just shows a value or a range of values (column C) as a note to myself. What I need it to do is reference the Items sheet and generate a random result based on a range of values on that table (column F). For example, if the % for "gems" result on the Chart sheet was 62%, it would reference the cost column (column F) for the gems data on the Items sheet. It would reference each row of gems that have a cost between 81 gp - 160 gp (because the result was 62%) and randomly select a "gem" from those rows.
4. It then repeats the process, but this time for column H on the "Random Treasure Table" and follows the same steps for the appropriate charts.
5. One difference is that the result for column H might require the generator to perform the action multiple times. So if the result is for "1d3 mundane" for example, it would generate 3 results from the "Mundane Item Generation" group.
6. Another difference for these charts is that each result (mundane, minor, medium, & major) will reference multiple other tables as explained below:
mundane
References the "Mundane Item Generation" group on the Charts sheet. The first table in the group is a list of potential items. A % is "rolled" (as in column B) to determine which secondary table to roll on.
Mundane Armor and shields:
For example, if the result was 18-42 for "Armors", then it references the "Mundane Items (Armors)" group on the Charts sheet.
It then "rolls" a % between 1-100 (as in column B) on the "Mundane Items (Armors)" table and references the appropriate row. At this point, it is similar to the "gems" example, except that it references the "Mundane, Armors" table on the Items sheet and generates a random result based on a range of values on the that sheet (column F).
The "Mundane Items (Armors)" table can also have a result (92-100) "Roll on Unusual Material table". This requires an additional "roll" on the "Mundane Items (Armor Unusual Materials)". This result references the appropriate table on the Items sheet ("Mundane, Armors, Unusual Material" table).
The "Mundane Item Generation" group items called "Alchemical items & Tools and gear" work the same as the gems and art tables. The "Weapons" item works similar to the Armors group, but as follows:
Mundane Weapons:
It "rolls" a % between 1-100 (as in column B) on the "Mundane Items (Weapons)" table on the Charts sheet and references the appropriate row. If the result was between 01-70 (Melee weapon), then on the "Mundane, Melee Weapons" table from the Items sheet, it randomly selects from all of the rows for that group. If the result was between 71-90 (Ranged weapon), then on the "Mundane, Ranged Weapons" table from the Items sheet, it randomly selects from all of the rows for that group.
If the result was between 91-100, it works the same way it works on the Armors example, except that it first rolls again to determine if the result is 01-70 (Melee weapon) or 71-90 (Ranged weapon), and then it rolls on the corresponding Melee or Ranged "Unusual Materials" table on the Charts sheet, followed by generating a random result based on a range of values (column F) corresponding to the Melee or Ranged "Unusual Materials" table on the Items sheet.
minor, medium, & major
These all work the same, but I will just explain how the "minor" group works.
References the "Minor Magic Item Generation" group on the Charts sheet. The first table in the group is a list of potential items. A % is "rolled" (as in column B) to determine which secondary table to roll on.
The "Minor Item Generation" group items called "Potions, Rings, Rods, Staffs, Wands, & Wondrous items" work the same as the gems and art tables. I will explain how the "Armor and shields, Weapons, and Scrolls (1d4 spells)" work:
Minor Armor and shields:
If the result was 01-10 for "Armor and shields", then it references the "Armor and Shields (Minor)" group on the Charts sheet. It then "rolls" a % between 1-100 (as in column B) on the "Armor and Shields (Minor)" table and references the appropriate row.
If the result is between 61-80 (+1 armor), it then references the "Mundane Items (Armors)" table on the Charts sheet and follows the exact same procedure as explained above when rolling for mundane items. The only difference is that the final result will start by displaying a "+1" before the name. So lets say we had the same results as when we rolled the mundane item, this minor item result would be "+1 Armor Unusual Material 8".
The "Armor and Shields (Minor)" table can have a result (92-100) "Roll again & roll on Special Ability table". This requires an additional "roll" on the "Armor and Shields (Minor)" table to determine if the result is +1 shield, +1 armor, +2 shield, or +2 armor (following the same steps as before), along with a roll on the "Armor Special Abilities (Minor)" table (if armor) or "Shield Special Abilities (Minor)" table (if shield) on the Charts sheet. Both of these results references the appropriate tables on the Items sheet ("Armor, Magic Special Ability" table or "Shield, Magic Special Ability" table). It will randomly select from the rows containing a value between the range given from the table on the Charts sheet as it does in previous examples.
One difference is that the "Armor Special Abilities (Minor)" and "Shield Special Abilities (Minor) tables can have a result of 100 (Roll twice again) which means that it "rolls" twice on that table so that the final result has two options selected from the rows on the "Armor, Magic Special Ability" or "Shield, Magic Special Ability" table on the Items chart.
Another possibility on the "Armor and Shields (Minor)" table is having a result (88-89) "Roll on Specific Armor table" or (90-91) "Roll on Specific Shield table". If it was (88-89) "Roll on Specific Armor table", this requires a "roll" on the "Specific Armors (Minor)" table on the Charts sheet. It then references the group of rows on the Items sheet titled "Armor, Magic Specific" and performs the same action as when rolling on the gems or art tables (referencing the rows of the appropriate range values in column F).
Minor Weapons:
If the result was 11-20 for "Weapons", then it references the "Weapons (Minor)" group on the Charts sheet. It then "rolls" a % between 1-100 (as in column B) on the "Weapons (Minor)" table and references the appropriate row.
If the result is between 01-70 (+1 weapon & roll on Mundane Items (Weapons)), it then references the "Mundane Items (Weapons)" table on the Charts sheet and follows the exact same procedure as explained above when rolling for mundane items. The only difference is that the final result will start by displaying a "+1" before the name. So lets say we had the same results as when we rolled the mundane weapon, this minor item result would be "+1 Weapon Unusual Material 7" or "+1 Melee Weapon 3".
The "Weapons (Minor)" table can have a result (91-100) "Roll again & roll on Special Ability table". This requires an additional "roll" on the "Weapons (Minor)" table to determine if the result is +1 weapon, or +2 weapon (following the same steps as before), along with a roll on the "Weapon Special Abilities (Minor)" table on the Charts sheet. This result references the "Weapons, Magic Special Ability" table on the Items sheet. It will randomly select from the rows containing a value between the range given from the table on the Charts sheet as it does in previous examples.
One difference is that the "Weapon Special Abilities (Minor)" table can have a result of 100 (Roll twice again) which means that it "rolls" twice on that table so that the final result has two options selected from the rows on the "Weapons, Magic Special Ability" table on the Items chart. This works just like when rolling on the "Armor Special Abilities (Minor)" table on the Charts sheet.
Another possibility on the "Weapons (Minor)" table is having a result (86-90) "Roll on Specific Weapon table". This requires a "roll" on the "Specific Weapons (Minor)" table on the Charts sheet. It then references the group of rows on the Items sheet titled "Weapons, Magic Specific" and performs the same action as when rolling on the gems or art tables (referencing the rows of the appropriate range values in column F).
Scrolls (1d4 spells):
If the result was 51-65 for "Scrolls (1d4 spells)", it first determines how many times to perform this operation, up to 4 times (hence the 1d4). It references the "Scrolls (Minor)" group on the Charts sheet that amount of times. It then "rolls" a % between 1-100 (as in column B) on the "Scrolls (Minor)" table and references the appropriate row.
It "rolls" a % between 1-100 (as in column B) on the "Scrolls (Minor)" table on the Charts sheet and references the appropriate row. If the result was between 01-70 (Acane), it then references the "Arcane Scroll Spell Levels (Minor)" table on the Charts sheet. If the result was between 71-100 (Divine), it then references the "Divine Scroll Spell Levels (Minor)" table on the Charts sheet.
The Column B in the "Arcane Scroll Spell Levels (Minor)" and "Divine Scroll Spell Levels (Minor)" tables indicates which table to "roll" on in the Items sheet. For example, result 01-05 - 0 (CL 1) on the "Arcane Scroll Spell Levels (Minor)" table requires a "roll" on the "Scrolls, Arcane 0 (CL 1)" table on the Items sheet. Similarly, a result 01-05 - 0 (CL 1) on the "Divine Scroll Spell Levels (Minor)" table requires a "roll" on the "Scrolls, Divine 0 (CL 1)" table on the Items sheet. It then randomly selects from all of the rows for that group on th Items sheet.
So to begin, I have a sheet that contains a long list of data that I have compiled. There are around 7000 rows (names) and 6 columns (data pertaining to those names).
I was wondering if someone would work with me to create a random generator using this data that works completely in an excel sheet. My ability to write formulas or VBA is limited, but I am great at following directions.
Here is a sample excel file that I created that contains sheets with a few rows of data for testing purposes (the values are in no way accurate):
http://www.rpglocker.com/DnD/Random Generator.xlsx
Here is how the generator would work:
I am not sure what the best approach would be as far as functionality and layout goes, but I went ahead and created 3 sheets: Generator, Items, and Charts.
Generator is the User screen.
Items is the database.
Charts is the operations.
I would like to be able to edit this generator on my own to include all 7000+ rows and also to add/adjust the data as needed in the future.
1. From the Generator sheet, you select from a dropdown menu a number from 1-30 which correlates to the "Level" in column B on the "Random Treasure Table" of the Charts sheet. It references those rows for the treasure being generated.
2. From the "Random Treasure Table" of the Charts sheet, it "rolls" a % between 1-100 (as in column C) and references the appropriate row. It generates a value from data in column D (Coins).
3. It then "rolls" again a % between 1-100 (as in column E) and references the appropriate row. It generates a value from data in column F (Goods).
3a. If the result is "gem", it references the group of rows on the Items sheet titled "Gems Generation". If the result is "art", it references the "Art Objects Generation" rows (also grouped).
3b. It then "rolls" a % between 1-100 (as in column B) on the gem or art table and references the appropriate row. Currently, the table just shows a value or a range of values (column C) as a note to myself. What I need it to do is reference the Items sheet and generate a random result based on a range of values on that table (column F). For example, if the % for "gems" result on the Chart sheet was 62%, it would reference the cost column (column F) for the gems data on the Items sheet. It would reference each row of gems that have a cost between 81 gp - 160 gp (because the result was 62%) and randomly select a "gem" from those rows.
4. It then repeats the process, but this time for column H on the "Random Treasure Table" and follows the same steps for the appropriate charts.
5. One difference is that the result for column H might require the generator to perform the action multiple times. So if the result is for "1d3 mundane" for example, it would generate 3 results from the "Mundane Item Generation" group.
6. Another difference for these charts is that each result (mundane, minor, medium, & major) will reference multiple other tables as explained below:
mundane
References the "Mundane Item Generation" group on the Charts sheet. The first table in the group is a list of potential items. A % is "rolled" (as in column B) to determine which secondary table to roll on.
Mundane Armor and shields:
For example, if the result was 18-42 for "Armors", then it references the "Mundane Items (Armors)" group on the Charts sheet.
It then "rolls" a % between 1-100 (as in column B) on the "Mundane Items (Armors)" table and references the appropriate row. At this point, it is similar to the "gems" example, except that it references the "Mundane, Armors" table on the Items sheet and generates a random result based on a range of values on the that sheet (column F).
The "Mundane Items (Armors)" table can also have a result (92-100) "Roll on Unusual Material table". This requires an additional "roll" on the "Mundane Items (Armor Unusual Materials)". This result references the appropriate table on the Items sheet ("Mundane, Armors, Unusual Material" table).
The "Mundane Item Generation" group items called "Alchemical items & Tools and gear" work the same as the gems and art tables. The "Weapons" item works similar to the Armors group, but as follows:
Mundane Weapons:
It "rolls" a % between 1-100 (as in column B) on the "Mundane Items (Weapons)" table on the Charts sheet and references the appropriate row. If the result was between 01-70 (Melee weapon), then on the "Mundane, Melee Weapons" table from the Items sheet, it randomly selects from all of the rows for that group. If the result was between 71-90 (Ranged weapon), then on the "Mundane, Ranged Weapons" table from the Items sheet, it randomly selects from all of the rows for that group.
If the result was between 91-100, it works the same way it works on the Armors example, except that it first rolls again to determine if the result is 01-70 (Melee weapon) or 71-90 (Ranged weapon), and then it rolls on the corresponding Melee or Ranged "Unusual Materials" table on the Charts sheet, followed by generating a random result based on a range of values (column F) corresponding to the Melee or Ranged "Unusual Materials" table on the Items sheet.
minor, medium, & major
These all work the same, but I will just explain how the "minor" group works.
References the "Minor Magic Item Generation" group on the Charts sheet. The first table in the group is a list of potential items. A % is "rolled" (as in column B) to determine which secondary table to roll on.
The "Minor Item Generation" group items called "Potions, Rings, Rods, Staffs, Wands, & Wondrous items" work the same as the gems and art tables. I will explain how the "Armor and shields, Weapons, and Scrolls (1d4 spells)" work:
Minor Armor and shields:
If the result was 01-10 for "Armor and shields", then it references the "Armor and Shields (Minor)" group on the Charts sheet. It then "rolls" a % between 1-100 (as in column B) on the "Armor and Shields (Minor)" table and references the appropriate row.
If the result is between 61-80 (+1 armor), it then references the "Mundane Items (Armors)" table on the Charts sheet and follows the exact same procedure as explained above when rolling for mundane items. The only difference is that the final result will start by displaying a "+1" before the name. So lets say we had the same results as when we rolled the mundane item, this minor item result would be "+1 Armor Unusual Material 8".
The "Armor and Shields (Minor)" table can have a result (92-100) "Roll again & roll on Special Ability table". This requires an additional "roll" on the "Armor and Shields (Minor)" table to determine if the result is +1 shield, +1 armor, +2 shield, or +2 armor (following the same steps as before), along with a roll on the "Armor Special Abilities (Minor)" table (if armor) or "Shield Special Abilities (Minor)" table (if shield) on the Charts sheet. Both of these results references the appropriate tables on the Items sheet ("Armor, Magic Special Ability" table or "Shield, Magic Special Ability" table). It will randomly select from the rows containing a value between the range given from the table on the Charts sheet as it does in previous examples.
One difference is that the "Armor Special Abilities (Minor)" and "Shield Special Abilities (Minor) tables can have a result of 100 (Roll twice again) which means that it "rolls" twice on that table so that the final result has two options selected from the rows on the "Armor, Magic Special Ability" or "Shield, Magic Special Ability" table on the Items chart.
Another possibility on the "Armor and Shields (Minor)" table is having a result (88-89) "Roll on Specific Armor table" or (90-91) "Roll on Specific Shield table". If it was (88-89) "Roll on Specific Armor table", this requires a "roll" on the "Specific Armors (Minor)" table on the Charts sheet. It then references the group of rows on the Items sheet titled "Armor, Magic Specific" and performs the same action as when rolling on the gems or art tables (referencing the rows of the appropriate range values in column F).
Minor Weapons:
If the result was 11-20 for "Weapons", then it references the "Weapons (Minor)" group on the Charts sheet. It then "rolls" a % between 1-100 (as in column B) on the "Weapons (Minor)" table and references the appropriate row.
If the result is between 01-70 (+1 weapon & roll on Mundane Items (Weapons)), it then references the "Mundane Items (Weapons)" table on the Charts sheet and follows the exact same procedure as explained above when rolling for mundane items. The only difference is that the final result will start by displaying a "+1" before the name. So lets say we had the same results as when we rolled the mundane weapon, this minor item result would be "+1 Weapon Unusual Material 7" or "+1 Melee Weapon 3".
The "Weapons (Minor)" table can have a result (91-100) "Roll again & roll on Special Ability table". This requires an additional "roll" on the "Weapons (Minor)" table to determine if the result is +1 weapon, or +2 weapon (following the same steps as before), along with a roll on the "Weapon Special Abilities (Minor)" table on the Charts sheet. This result references the "Weapons, Magic Special Ability" table on the Items sheet. It will randomly select from the rows containing a value between the range given from the table on the Charts sheet as it does in previous examples.
One difference is that the "Weapon Special Abilities (Minor)" table can have a result of 100 (Roll twice again) which means that it "rolls" twice on that table so that the final result has two options selected from the rows on the "Weapons, Magic Special Ability" table on the Items chart. This works just like when rolling on the "Armor Special Abilities (Minor)" table on the Charts sheet.
Another possibility on the "Weapons (Minor)" table is having a result (86-90) "Roll on Specific Weapon table". This requires a "roll" on the "Specific Weapons (Minor)" table on the Charts sheet. It then references the group of rows on the Items sheet titled "Weapons, Magic Specific" and performs the same action as when rolling on the gems or art tables (referencing the rows of the appropriate range values in column F).
Scrolls (1d4 spells):
If the result was 51-65 for "Scrolls (1d4 spells)", it first determines how many times to perform this operation, up to 4 times (hence the 1d4). It references the "Scrolls (Minor)" group on the Charts sheet that amount of times. It then "rolls" a % between 1-100 (as in column B) on the "Scrolls (Minor)" table and references the appropriate row.
It "rolls" a % between 1-100 (as in column B) on the "Scrolls (Minor)" table on the Charts sheet and references the appropriate row. If the result was between 01-70 (Acane), it then references the "Arcane Scroll Spell Levels (Minor)" table on the Charts sheet. If the result was between 71-100 (Divine), it then references the "Divine Scroll Spell Levels (Minor)" table on the Charts sheet.
The Column B in the "Arcane Scroll Spell Levels (Minor)" and "Divine Scroll Spell Levels (Minor)" tables indicates which table to "roll" on in the Items sheet. For example, result 01-05 - 0 (CL 1) on the "Arcane Scroll Spell Levels (Minor)" table requires a "roll" on the "Scrolls, Arcane 0 (CL 1)" table on the Items sheet. Similarly, a result 01-05 - 0 (CL 1) on the "Divine Scroll Spell Levels (Minor)" table requires a "roll" on the "Scrolls, Divine 0 (CL 1)" table on the Items sheet. It then randomly selects from all of the rows for that group on th Items sheet.
