Announcement

Collapse
No announcement yet.

OpenOffice Spreadsheet question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • EPW
    replied
    Pro-Tip: Charts are absolute crap in OpenOffice.

    Leave a comment:


  • EPW
    replied
    I'm getting some weird behavior if I use the > or < operators and the ROW() function together if I get data from another page.

    For instance, the formula =$Rules.$E$6>2 always returns true no matter the value of $Rules.$E$6. Is this a glitch in OO or is there a logical explanation?

    Edit: Never mind I'm an idiot. E6 was formatted as text.

    Leave a comment:


  • EPW
    replied
    Originally posted by snoopy369
    Index and Match can be used to find a particular element of an array, ie, if you have 20 cells, you can use them to find the 13th element. In the case you described, you would use them to select the first or second or whatever out of your lookup-returned array.
    Figured it out, that works a lot better for the semi final matchups

    Leave a comment:


  • snoopy369
    replied
    Index and Match can be used to find a particular element of an array, ie, if you have 20 cells, you can use them to find the 13th element. In the case you described, you would use them to select the first or second or whatever out of your lookup-returned array.

    Leave a comment:


  • EPW
    replied
    Originally posted by snoopy369
    You used INDEX and MATCH, I presume?
    I saw this solution also, but honestly I didn't understand it never having used those functions before.

    Leave a comment:


  • snoopy369
    replied
    Ah, that's another solution (though messier, but it does certainly work).

    Leave a comment:


  • EPW
    replied
    Originally posted by snoopy369
    You used INDEX and MATCH, I presume?
    No, I added another(hidden) column in front of the rankings which labels those that are tied 1,2,...,n.

    Leave a comment:


  • snoopy369
    replied
    You used INDEX and MATCH, I presume?

    Leave a comment:


  • EPW
    replied
    nm found the solution online

    Leave a comment:


  • EPW
    replied
    Originally posted by snoopy369
    From http://documentation.openoffice.org/...alcMacros.pdf:

    Code:
    Function SumCellsAllSheets()
    Dim TheSum As Double
    Dim i As integer
    Dim oSheets
    Dim oSheet
    Dim oCell
    oSheets = ThisComponent.getSheets()
    For i = 0 To oSheets.getCount() - 1
    oSheet = oSheets.getByIndex(i)
    oCell = oSheet.getCellByPosition(0, 1) ' GetCell A2
    TheSum = TheSum + oCell.getValue()
    Next
    SumCellsAllSheets = TheSum
    End Function
    The important part is
    oSheets.getCount()
    and
    oSheets.getByIndex(i)

    If you want the last sheet, you would use
    oSheets.getByIndex(oSheets.getCount()-1)
    (zero-based math, I presume).

    My guess is that this sheet then has name of
    oSheets.getByIndex(oSheets.getCount()-1).getName()
    or possibly .Name() .Name etc. - I don't know Sun basic, but it's something in that range.
    I ended up just using the record macro ability to do what was needed. Thanks though.

    I have another question, I am trying to add a playoff sheet to my gladiator spreadsheet but I am running into difficulties. First, I am trying to deal with 4th place ties(though only a two way tie at the moment). So lets say Theben and Snoopy are tied for fourth, First I use Countif to see how many players qualify for the semifinals. If the number is greater than 4 I know a tie breaker match is needed; this part works fine. Next, I use SMALL to return the rank of those tied; this would be 4 with a 2 way tie but would be a different number with 3 or more way ties. This part alse works. Next, I use VLookup to return the name of the players who are tied by searching the rank column for the number returned by SMALL. This works for the first player on the list, but Vlookup always returns the first match it comes across, so I can never get the second player. Also assume that the rankings are unsorted.

    Leave a comment:


  • snoopy369
    replied
    From http://documentation.openoffice.org/...alcMacros.pdf:

    Code:
    Function SumCellsAllSheets()
    Dim TheSum As Double
    Dim i As integer
    Dim oSheets
    Dim oSheet
    Dim oCell
    oSheets = ThisComponent.getSheets()
    For i = 0 To oSheets.getCount() - 1
    oSheet = oSheets.getByIndex(i)
    oCell = oSheet.getCellByPosition(0, 1) ' GetCell A2
    TheSum = TheSum + oCell.getValue()
    Next
    SumCellsAllSheets = TheSum
    End Function
    The important part is
    oSheets.getCount()
    and
    oSheets.getByIndex(i)

    If you want the last sheet, you would use
    oSheets.getByIndex(oSheets.getCount()-1)
    (zero-based math, I presume).

    My guess is that this sheet then has name of
    oSheets.getByIndex(oSheets.getCount()-1).getName()
    or possibly .Name() .Name etc. - I don't know Sun basic, but it's something in that range.

    Leave a comment:


  • snoopy369
    replied
    In VBA it's:
    Activesheet.Name

    I couldn't tell you in OO scripting. If you can track down Comrade Tassadar I think he does some OO scripting.

    Also, in Excel you have two options for any given sheet.

    Let's say I have three sheets, "First", "Second", and "EPW".

    I could say
    Sheets("First").Range("A1")
    or
    Sheet1.Range("A1")

    and they would be identical; Sheet1 means it was the first sheet in the workbook (ever - that does not dynamically change, or at least not consistently).

    Leave a comment:


  • EPW
    replied
    Really all I need is a function that returns the name of the last sheet. The SHEETS() function doesn't seem to work.

    Leave a comment:


  • EPW
    replied
    Is there anyway to dynamically add a sheet?

    Leave a comment:


  • Badfuzzy
    replied
    yes

    Leave a comment:

Working...
X