Announcement

Collapse
No announcement yet.

OpenOffice Spreadsheet question

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

  • #16
    You can do 4, of course (base format plus 3 conditional formats).

    I understand precisely what you want However, the problem is that a cell stores a number or a letter, or several of one or the other; or a formula. It does not store anything else. Things like color, font, etc. are metadata that are not directly accessible inside the cell.

    The way to do the row is, of course, with a conditional format on the entire row

    Do a formula-based conditional format (not just a IF CELL = VALUE format) where you say:
    =IF($A1="BLUE",TRUE,FALSE)
    as the conditional format condition (I don't know OO specifically, but it tends to imitate excel in such).

    Then apply that to the entire row. (Replace A1 with the value driver.)
    <Reverend> IRC is just multiplayer notepad.
    I like your SNOOPY POSTER! - While you Wait quote.

    Comment


    • #17
      Wow that worked, thanks.
      "

      Comment


      • #18
        No problem

        If you want to do it with macros, use the macro recorder to learn how the color is applied, and then use an IF statement to apply it. I don't know OO's macro language, but I imagine that's possible in it.
        <Reverend> IRC is just multiplayer notepad.
        I like your SNOOPY POSTER! - While you Wait quote.

        Comment


        • #19
          One more question:

          I have a formula that I want to apply to many rows, where it plugs in values that are also in the same row. However, I also have "constants" values that a may want to change in the future in the equations. So when i drag the equation into the other cells it also changed the cell it tries to read the constants from. Any way around this?
          "

          Comment


          • #20
            $

            A1 dragged down will become A2, dragged right B1
            $A1 dragged down will become $A2, dragged right $A1.
            A$1 dragged down will become A$1, dragged right B$1.
            $A$1 dragged down or right stays $A$1.
            <Reverend> IRC is just multiplayer notepad.
            I like your SNOOPY POSTER! - While you Wait quote.

            Comment


            • #21
              amazing
              "

              Comment


              • #22
                Hmm, anyway to drag conditional formatting or do I have to select all the rows first?
                "

                Comment


                • #23
                  Dunno. In Excel there are several ways to do it depending on which version of excel...

                  Can you copy and then paste special the formats, perhaps?
                  <Reverend> IRC is just multiplayer notepad.
                  I like your SNOOPY POSTER! - While you Wait quote.

                  Comment


                  • #24
                    yes
                    "

                    Comment


                    • #25
                      Is there anyway to dynamically add a sheet?
                      "

                      Comment


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

                        Comment


                        • #27
                          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).
                          <Reverend> IRC is just multiplayer notepad.
                          I like your SNOOPY POSTER! - While you Wait quote.

                          Comment


                          • #28
                            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.
                            <Reverend> IRC is just multiplayer notepad.
                            I like your SNOOPY POSTER! - While you Wait quote.

                            Comment


                            • #29
                              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.
                              "

                              Comment


                              • #30
                                nm found the solution online
                                "

                                Comment

                                Working...
                                X