Game in the Brain

Stuck, looking for Spreadsheet functions

I’m stuck. I’ve been working on a ship building spreadsheet for ISW traveller. Using it as an exercise to learn spreadsheets.

how to reference several arrays? When choosing a shape, it narrows the streamlining options for the ship. Some structures don’t have any streamlining options, like diffused hull ships. Now I want to make a list field in the Form control that changes its refence array as it changes choice of shape.
Vlookup is not working. There is something strange happening in my spreadsheet. Vlookup is not working, I found a long winded work around to the problem above but I can’t execute it because all my vlookup functions are not working (error:502). I don’t get it, some vlookup formulas are working others are not. I double and tripple checked, rewrote them again and reread everything. No clue why.
Recalculate randbetween function. F9 is supposed to recaclualte certain randbetween but it is not working. Looked around and all the help I found online say F9 to recalculate. Anyway, I’m supposed to make a macro targeting one cell, for recalculation down the line. I need to be able to learn to do this If I plan to make a character generator spreadsheet.
Export or Reference macro or function. After all the stats are generated, I need to be able to express all of the data into a stat block. Ideally all can be exported to a text file to be easily cut and pasted for an article. The function list is not detailed in description, I really need to find the time to go through function per function and look at what they really do in detail.
Currently i have colds and a bit of a fever, but the head ache its giving is not helping and stressing me even more.
Other dream spreadsheet projects:
  • Harn/GURPS character creation spreadsheet. Using Harn character generator but automatically converting to GURPS equivalent.
  • System Generator > a more detailed economic system generator
  • Trade Tables Generator
Reading the Black Company Again. Half way through Shadows Linger.

6 responses to “Stuck, looking for Spreadsheet functions”

  1. Dan Eastwood Avatar
    Dan Eastwood

    Are you using Open Office?
    http://www.oooforum.org/forum/viewtopic.phtml?t=55303

  2. Dan Eastwood Avatar
    Dan Eastwood

    As for the problem of referencing several arrays, I think this is possible, but it is *not* simple. A friend uses this for his own ship design spreadsheet. You might try posting a question in the Squadron Strike section at adastragames.com/discus.html

  3. Ken Burnside Avatar
    Ken Burnside

    First, what you should be doing for your array problem:

    VLOOKUP(Input_Value;Array_Name;Column;FALSE)

    Input_Value: What the user enters the match first column. It can be a cell reference, or better, a named cell.

    Array_Name: The range of cells defining the VLOOKUP function. This can also be a named range of cells.

    Column: The column of the table defined above; it must be 2 or greater. This can be set by pointing at a cell reference.

    FALSE: This forces VLOOKUP to do an exact match. Most VLOOKUP errors boil down to people forgetting to do this.

    What you appear to need is a two axis lookup.

    The first axis is the size of the ship. The second axis is the aerodynamic dimensions of the ship.

    The most flexible way to do this is to use INDEX() and MATCH() functions.

    INDEX(Array_Name,Column_Num,Row_Num) will return the value found at the intersection of column and row in the named range.

    MATCH(Lookup_Value;Array_Name;FALSE) is basically VLOOKUP constrained to only a single row or column. You ca use MATCH functions to return the rows and columns to an INDEX function…and you can use named ranges for the two axes of your table as your array names for the MATCH() function.

    You can also use the List type of data validation, populated with those two array names, to constrain user inputs.

    I use these techniques a lot – which is why Dan pointed me at your problem.

  4. justin aquino Avatar
    justin aquino

    Thanks DC and Ken!
    It got me over my stump, and I learned new spreadsheet tricks.

  5. Dan Eastwood Avatar
    Dan Eastwood

    Burnside to the rescue!

    Ken's spreadsheets will make your brain explode. 🙂

  6. Dan Eastwood Avatar
    Dan Eastwood

    Burnside to the rescue!

    Ken's spreadsheets will make your brain explode. 🙂

Leave a Reply

More Articles & Posts

Search

Popular Posts

Categories

Archives

Tags

Follow Us