Drop-Down menus?

[Illtium]

Hey guys, I am creating data sheets for a game that I am in currently in the process of making, it is a table-top game with lots of statistics etc a bit like dungeons and dragons in that sense. Now for the question.

My game is sea based and the statistics for each ship are slightly modified depending on different things. First I outline the four sizes of ship on sheet A, the 4 variations of each ship size are on sheet B, the 6 factions and their modifiers are on sheet C so the set up goes like this:

Sheet1: sets the statistics for the 4 ship sizes,
Sheet2: is a sheet of modifiers to those statistics depending on what type of ship is chosen,
Sheet3: is a sheet of modifiers to those statistics depending on which of the 6 factions is chosen.

What I want to make is a Sheet 4 with a drop down menu box to choose ship size, a drop down menu to choose ship type and a drop down menu to choose the faction, once all three are chosen, a finish button will make the calculation that will fill in the blank ship template on sheet4 with the ship statistics with the two modifications to them.

So far I have sheets1,2,3 set up with all of the data, sheet 4 has a blank template.

I guess there would be a lot of IF statements like on drop down menu 1, (IF [1] then sheet1 C4 ELSE IF [2] then sheet1 D4 etc...

In writing this, I have thought of an extremely long winded way of doing this where each of the empty template boxes has a huge calculation in but I was wondering if there would be an easier way of doing it.

[Villeroy]

Always use some database for lists and mapping list items to each other. Never use spreadsheets.
In fact OOo Base is the worst database application on the planet, but way better for this kind of jobs than any spreadsheet will ever be.

[Illtium]

Oh okay, The reason I used it was because of the ability to use equations as after the base stats I have two separate tables that add or subtract numbers from the base stats to get the resulting stats.

In my memory for the databases you have to input the records for every result and if that was the case 4 ship sizes with 4 variants of each size and 6 factions means I would manually have to type out (4x4)x6= 96 records. I was hoping this would be easier.

[Villeroy]

You could generate 96 records in SQL, you could generate them in a spreadsheet and copy over, you could simply use 2 fields for ship type and size.
I'm 100% sure that a database is the right tool IF you have to collect data in growing lists of measurements, assignments, items or anything like that.
I know that most people do it in a spreadsheet but a forum like this one is not the right place to spoon feed the very same fundamentals of spreadsheet editing every day to every new user.

[Illtium]

The last line was very un-needed there simply saying 'look at the tutorials for the basics' is actually more polite. Putting that asside, I guess another program could be used, I just used the spreadsheet because of ease of editing without too much knowledge. I simply asked if there was a way to do it with spreadsheet, if not I'll have to do the 96 seperate results manually because I don't possess the knowledge to do the more complicated ways using SQL databases or the database programs...

[Villeroy]

The assumption that you can edit a spreadsheet like a brain dead is true, but you won't get reliable results then.
You are just too lazy to learn anything about the tools you are trying to use. A spreadsheet includes some kind of simplified programming language.

(IF [1] then sheet1 C4 ELSE IF [2] then sheet1 D4 etc...

Quite obviously, you never wrote a single spreadsheet formula in your life.
Back to the drawing board. Do your home work.

[acknak]

I don't think this sounds so unreasonable for a spreadsheet--at least as a prototype.The lists of stats don't need to be dynamic--that you would add new stats as part of the game--only that the exact values for a particular ship depend on three parameters, right?

I think this might be workable as some kind of support for a game, but not if you're expecting to manage a whole fleet of ships--then you really will need a database. And Calc (and OOo Base as well) makes a lousy base for building an application.

If you want to try it out, it's not hard to build up a prototype. The way this is usually done is through a lookup table instead of a long list of if...then clauses.

So for a particular stat in the final sheet, the formula would look essentially like this:
Final_Stat = Base_Value * Size_Modifier * Type_Modifier * Faction_Modifier

Each of the modifier values can be pulled from the appropriate tables using a VLOOKUP function that takes the parameter selected in the drop down list and uses it as a key to retrieve the appropriate modifier. The lookup for the size modifier will be something like this:
=VLOOKUP( Selected_Size; Size_Table; Result_Column; 0)

You can find documentation on the VLOOKUP function in the online help, or better, online in the OOo wiki: http://wiki.services.openoffice.org/wik ... P_function

The drop down lists are available through the Data > Validity > Criteria: Cell Range feature.

The (draft) Calc user guide is here: http://wiki.services.openoffice.org/wik ... Calc_Guide

If you try a search here, you should find dozens of discussions of the details for doing this, including example documents in some threads. This particular approach comes up at least once a day (so it seems), which may provide some context to the reluctance to open the subject again.

Searching for "vlookup" finds almost 600 threads; "vlookup +validity" only 16. That might be a reasonable list to start with.

[Illtium]

So far I have a simple (in my mind) but long-winded way of doing it. Thank you acknak for your advice, yes it is a support, to quickly pull up the final list of stats so they can be written down on a stat card for that ship. I just needed some way of being able to get the final calculation quickly without sitting there and having to hand add three sets of stats together.

Yes there will be a fleet of ships for each side but as said, as soon as I have the result for one type, its gets written and i wont have to do that again.

Sadly right now I am using a lot of IF statements, it took me a while through trial and error to figure out that for some reason wasn't allowing me to refferences data on other sheets, so a quick shuffle puts all three sheets into one sitting side-by-side and this seems to allow it to work.

I don't need the sheet to store the final results each time, I am just using it to call them up to view.

What I have done is made a small table with 6 columns, two for each of the sets of data, only one of the two being the data input column, the other just being lables, so by putting a 1 in the column in the row of the size you want will call the stat for that size, the next column being type and it will call that modifier and the third being the faction,

I then have an empty stat table that has the calculations in that will show the final results when the 1s are put into the table.

The good thing is the way the IF statements are done, the ELSE statement is the next IF statement so if more than one '1' are entered into the column, it only submits the highest in that column.

It seems to work thus far.