Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Preserve Formula References After Sort

  1. #1
    Join Date
    16th September 2006
    Posts
    28

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

  4. #4
    Join Date
    16th September 2006
    Posts
    28

    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 24th, 2006 at 00:06.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.
    VB:
    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 24th, 2006 at 00: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.

  6. #6
    Join Date
    16th September 2006
    Posts
    28

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

  8. #8
    Join Date
    16th September 2006
    Posts
    28

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th September 2006
    Posts
    28

    Re: Preserve Formula References After Sort

    VB:
    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.)

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    16th September 2006
    Posts
    28

    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 at 01:23.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Sort Formula Results Where Formula Returns Blank Empty Text
    By Demons_traitor in forum EXCEL HELP
    Replies: 1
    Last Post: April 17th, 2008, 19:55
  2. Replies: 9
    Last Post: February 23rd, 2008, 01:07
  3. Sort & Retain Formulae References
    By steven314 in forum EXCEL HELP
    Replies: 2
    Last Post: September 15th, 2007, 04:41
  4. Change Formula References
    By markc in forum EXCEL HELP
    Replies: 3
    Last Post: August 1st, 2006, 14:38
  5. Replies: 7
    Last Post: August 31st, 2003, 03:56

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno