Announcement

Collapse
No announcement yet.

Preserve Formula References After Sort

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

  • Preserve Formula References After Sort

    I have noticed that if I create a row and put a reference formula in one of the cells (i.e. in Cell B3 I have "=B4", to show contents of B4), then after I do Sort and Row 3 moves else where, formula in what used to be B3 no longer points to B4, but rather to B<new row + 1>.

    Absolute reference doesn't seem to be an option (i.e. "=$B$4") since after sorting rows in question the cell references in my B cells do get screwed up. I guess I want to bind a certain cell to a certain other cell, and keep that bind no matter where I move the row.

    Any ideas?

    Thanks.

  • #2
    Re: Preserve Formula References After Sort

    Change your formulas such that the B3 formula would be
    =OFFSET(B3,1,0)

    These should always point to the cell below themselves after a sort.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Re: Preserve Formula References After Sort

      Hmmmm ... I think I read your question backwards. Onething you could do to ensure that a formula always points to the contents of the origibnal cell, is make the target cell a Named Range. (INSERT > NAME > DEFINE)

      For example, name cell B4 "MyB4Cell" (without the quotes) and in cell B3 enter
      =MyB4Cell
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Re: Preserve Formula References After Sort

        Thanks, I guess that would work.
        Is there a way to automatically define names for all the cells?
        In a sense I would like to be able to insert n new rows, fill in any appropriate values, then insert another row and have its column B cell show the contents of one of the previous new rows B cell value, preferably by me going to the cell, typing in = and then navigating to the needed cell and hitting Enter.

        Seems that the way you're suggesting would work, but I need some automated way of labeling every new cell in column B with a definite Name - it can be something as simple as Label<index>, where <index> refers to the index of row creation. I might start by applying this - I would guess it would have to be a Macro - to label all my existing col B cells, (this would label them B<index = row number>), then for every additional row I create (a row that has something input into it) I would get a new Name for cell B.
        Last edited by al1; October 23rd, 2006, 23:06.

        Comment


        • #5
          Re: Preserve Formula References After Sort

          Something like the following code would give the B column cell of each newly added row a unique name. (I think you can't just use the row number, because you might insert a new row 3 multiple times and this would cause a naming duplication.
          Code:
          Private Sub Worksheet_Change(ByVal Target As Range)
          Dim r As Long
              If Target.Cells.Count = Columns.Count * Target.Rows.Count And WorksheetFunction.CountA(Target) = 0 Then
                  For r = 1 To Target.Rows.Count
                      Cells(Target(1).Row + r - 1, "B").Name = "Cell" & Format(Now, "YYHHMMSS") & r
                  Next r
              End If
          End Sub
          NOTE: This event macro MUST be placed in the sheet's private VBA code module. Right click the sheet's tab and select "View code"
          Last edited by thomach; October 23rd, 2006, 23:23.
          Best Regards,
          Tom
          ---------------------------
          Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

          Comment


          • #6
            Re: Preserve Formula References After Sort

            Can I have two Worksheet_Change Subs, or do I need to insert this code inside Worksheet_Change Sub that I already have for that sheet?

            Comment


            • #7
              Re: Preserve Formula References After Sort

              You can only have one Worksheet_Change macro (per sheet). You need to insert all the relavent code inside the same macro.
              Best Regards,
              Tom
              ---------------------------
              Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

              Comment


              • #8
                Re: Preserve Formula References After Sort

                Great.
                Would you know how to add milliseconds to HHMMSS?
                This is so that in case i insert two rows in the same time.

                Comment


                • #9
                  Re: Preserve Formula References After Sort

                  Code:
                  Private Sub Worksheet_Change(ByVal Target As Range)
                      Dim r As Long
                      If Target.Cells.Count = Columns.Count * Target.Rows.Count And WorksheetFunction.CountA(Target) = 0 Then
                          For r = 1 To Target.Rows.Count
                              Cells(Target(1).Row - 1, "P").Copy Cells(Target(1).Row + r - 1, "P")
                              Cells(Target(1).Row - 1, "A").Copy Cells(Target(1).Row + r - 1, "A")
                              Cells(Target(1).Row + n - 1, "C").Name = "Cell" & Format(Now, "YYHHMMSS") & n
                          Next r
                      End If
                  End Sub
                  You know, strangely enough, I am not getting a name for the cell that I insert with this code. (The other two work great, but not Cells....Name one.)
                  It is still labeled as "C<current row>".
                  (Yes, I moved from B to C.)

                  Comment


                  • #10
                    Re: Preserve Formula References After Sort

                    I got it:
                    I had edited your code incorrectly, when I inserted it into the existing Worksheet_Change.

                    With this figured out, what is the quickest way to label all the cells in the rows that already exist (so that I can refer to named cells in those rows)?
                    Last edited by al1; October 24th, 2006, 00:23.

                    Comment


                    • #11
                      Re: Preserve Formula References After Sort

                      This macro (placed either in the sheet's module or a general module) should name all of the cells in column C that have a current entry.
                      Code:
                      Sub NameFilledCCells()
                      
                      Dim r As Long
                          For r = 2 To Cells(Rows.Count, 3).End(xlUp).Row
                              If Len(Cells(r, "C")) > 0 Then
                                  Cells(r, "C").Name = "Cell" & Format(Now, "YYHHMMSS") & r
                              End If
                          Next r
                      End Sub
                      Last edited by thomach; October 24th, 2006, 00:35.
                      Best Regards,
                      Tom
                      ---------------------------
                      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                      Comment


                      • #12
                        Re: Preserve Formula References After Sort

                        P.S. An VBA Editor hint. In the VBA editor do a TOOLS > OPTIONS > Editor (tab) and check "Require Variable Declaration"

                        This will place "Option Explicit" at the top of each of your VBA modules (at least new ones created after you set the feature). It will REQUIRE you to declare (DIM) your variables. It also will tell you if you try to run code with a varaible that has not been declared. For example, when you tried to run the code you posted with the n used instead of r, it would have gone into debug mode and pointed to that code row. (Unless you had DIMed "n" for use elsewhere in your code.)
                        Best Regards,
                        Tom
                        ---------------------------
                        Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                        Comment


                        • #13
                          Re: Preserve Formula References After Sort

                          Before I do that, would the cell formulas that reference those cells (i.e. =C<particular row number>) get automatically changed to be "=NewCellNameFromThisMacro"?
                          If I change the name of a cell using INSERT>NAME>DEFINE, the formulas do not update.

                          I suppose the real question is, is there a way to update all my current formulas that reference any cells in Col C so that they would refer to the name of that cell instead?
                          Last edited by al1; October 24th, 2006, 00:49.

                          Comment


                          • #14
                            Re: Preserve Formula References After Sort

                            There is a method (a keyboard shortcut, I think) for substituting cell names into formulas (replacing the cell addresses with their Name), but darned if I can ever remember what it is. (And my cheat sheet reference for it is not at me current location.)
                            Best Regards,
                            Tom
                            ---------------------------
                            Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                            Comment


                            • #15
                              Re: Preserve Formula References After Sort

                              Just found this in Walkenback's "Excel 2003, Power Programming with VBA" page 49.

                              "Start by selecting the range you want to modify. Then choose the Insert > Name > Apply command. In the Apply Names dialog box, select the names that you want to apply then click OK."
                              Best Regards,
                              Tom
                              ---------------------------
                              Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                              Comment

                              Working...
                              X