Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Track Changes->History (Excel 97)

    Hello,

    Is there anyway to alter the History page so that it always shows Column A of the worksheet it found the change on?

    You see the tracking would be perfect for me if it showed my Site # which is located in column A of each worksheet...

    Any ideas, or maybe someone has created an add-on that would do this?

    -Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Track Changes->History (Excel 97)

    I don't think there is a way to do it directly. You could copy it to a new worksheet, insert a new first column, and use the formula in column A2:
    <pre>=INDIRECT("'"&G2&"'!A1")</pre>

    copy it down and get a a revamped history

    or you could use this macro which does a similar thing via code. Change the name of the revamp history sheet as desired

    Steve

    <pre>Sub RevampedHistory()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim vHistory
    Dim rng As Range
    Dim rCell As Range

    Set wkb = ActiveWorkbook
    With wkb
    Set wks = .Worksheets("RevampHistory")
    vHistory = .Worksheets("History").UsedRange.Value
    End With
    With wks
    .Range("B1").Resize(UBound(vHistory, 1), UBound(vHistory, 2)).Value = vHistory
    Set rng = .Range(.Range("g2"), .Range("g2").End(xlDown))
    End With
    For Each rCell In rng
    rCell.Offset(0, -6) = wkb.Worksheets(rCell.Value).Range("a1")
    Next

    End Sub</pre>


  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Track Changes->History (Excel 97)

    I gave it a try Steve, but that just put the Header into Column A all the way down.

    I have included a very simplistic sample sheet. Since it won't save with the History, you will have to recreate that, but I have already made some changes, and would like to know if it is possible to somehow have it automatically (Or through a MACRO) put in the Site # on the page.....

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Track Changes->History (Excel 97)

    My code assumed (I guess I should have stated this) that you wanted the item from cell A1 of the changed sheet. If you want what is in the cell in column A of the row that is changed, replace that loop at the end with:

    <pre> For Each rCell In rng
    If Not IsEmpty(rCell.Offset(0, 1)) Then _
    rCell.Offset(0, -6) = wkb.Worksheets(rCell.Value). _
    Range(rCell.Offset(0, 1).Value).EntireRow.Range("a1")
    Next</pre>


    This gets the changed sheet and the changed row and selects the first cell in that row.
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Track Changes->History (Excel 97)

    Thank you so much Steve...

    That is EXACTLY what I was looking for.

Posting Permissions

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