Construct a basic database

I'm trying to construct a basic worksheet for use during game.

I need an Excel sheet that has a basic set of fields where the user can enter data. Once the data is entered, I want it to generate a list of completed sets of fields that will display when someone clicks on one of the completed fields on the list.

Basically, an Excel version of this
http://petteri.kamppuri.fidisk.fi/spellbookmaster/mac/

Yes, horribly nerdy, I know.

Originally Posted by Hoplite

I'm trying to construct a basic worksheet for use during game.

I need an Excel sheet that has a basic set of fields where the user can enter data. Once the data is entered, I want it to generate a list of completed sets of fields that will display when someone clicks on one of the completed fields on the list.

Basically, an Excel version of this
http://petteri.kamppuri.fidisk.fi/spellbookmaster/mac/

Yes, horribly nerdy, I know.

It's a bit difficult to understand exactly what you're after here, but I've made a stab at something which may set you on the general path.

Take a look at the attached and let me know if this is along the right lines. It allows you to select the Class and a relevant Spell for that Class and displays the Level and Details.

Originally Posted by Hoplite

That's very close to what I'm looking for. I might even be able to cannibalize the sheet to make what I need.

Can you tell me basically how you put that together?

Thank you

There are a few basic things that are required for this to function how it is:

1. Named Ranges
On the "Data Sheet" tab, you will see that all of the values under Class belong to a named range called "Class". Likewise, each of the values under Class have a Named Range named after it, which is the range of items belonging to each class (e.g. the Named Range called "Elf" contains all spells belonging to an Elf. Finally there is a Named Range for the range of Spell Names, Levels and Descriptions. This is used in the VLOOKUP.
Note: I have manually named the ranges, so if you add in additional data, these named ranges will have to be manually extended to take in the new data.

2. INDIRECT Method
On the "Lookup Sheet" tab, there are two dropdowns. The first Data Validation selects items from the "Class" Named Range. The second uses the INDIRECT method to populate the dropdown based on what Class you select. As per #1 above, if you select the class "Elf" from the first dropdown, the second dropdown is populated with items belonging to the "Elf" Named Range. So whatever you select in the Class dropdown, the Spell dropdown will look for a Named Range with the same name. This is why 2-word Classes cannot have a space (e.g. HarryPotter) because a Named Range cannot have a space in it.

3. VLOOKUP Function
On the "Lookup Sheet" tab the Level and Description fields are populated by performing a VLOOKUP to search for the Spell Name that you selected in the second dropdown. There is additional checking in the formula to ensure that if an error is returned (i.e. a #N/A is returned if the Spell Name cannot be found) then it returns a blank and therefore nothing in the cell. This just keeps things tidy.

4. Conditional Formatting
One of the "gotchas" of using INDIRECT to populate a second Data Validation dropdown is that when a user selects an option from the first dropdown, the second is populated, then they select an item from the second, all is good, but if they go back and select a different option from the first dropdown, the second value still sits there but is not a valid member of the first selection (e.g. select "Elf" in the Class dropdown, select "Tree Hugging" from the Spell dropdown, now select "Wizard" from the Class dropdown - "Tree Hugging" is not a valid option for the Wizard Class). To combat this, I have used Conditional Formatting to perform a VLOOKUP to search for the Spell chosen in the selected Class Named Range. If it isn't found then it turns the text white (the value still exists, but a user cannot see it and therefore thinks it has disappeared). In our example above, when you select "Wizard", the Conditional Formatting looks for the Spell "Tree Hugging" in the "Wizard" Named Range, and not finding it, turns the font white.
I have used this same Conditional Formatting on the Level and Description fields for the same reason.

So that is the "basic" explaination. If you want more detail, just shout. Also, if you want further help with your spready, let me know, or ping me a private message and I'd be happy to help build this for/with you.