Change events Updated to Summary Page

**Panther1** · 11-21-2009, 02:00 PM

Hello All,

I have had a TREMENDOUS amount of assistance from a true gentleman, Jaslake; aka John. In an attempt to quite monopolizing his time I wanted to throw this at the board.

I have a number of sub sheets with exact ranges of (B4,d4:b44, D44) update an individual cell (H6:H86) in the” summary” page. My attachment explains it in detail but currently I will show you the formula that resides in (H6) of "Summary". (H6) = Sheet "1". This formula only covers (B4,D4:B10,D10) for example purposes but I need it to run all the way through (B4,d4:b44, D44) for all 80 sheets.

=('1'!B4) & "-" & ('1'!D4) & " | " & ('1'!B5) & "-" & ('1'!D5) & "|" & ('1'!B6) & " - " & ('1'!D6) & "|" & ('1'!B7) & "-" & ('1'!D7) & " | " & ('1'!B8) & "-" & ('1'!D8) & "|" & ('1'!B9) & " - " & ('1'!D9) & "|" & ('1'!B10) & " - " & ('1'!D10).

Results look like this:

March 01, 2009-Today looks good | March 02, 2009-Today looks bad|March 03, 2009 - Today we had issues in Boston|March 04, 2009-Issues in Dallas | March 05, 2009-Issues In New York|March 06, 2009 - New York is complete|March 07, 2009 - Dallas is Complete

NOW, with this said what I really would like to happen, if it is possible, is to have the "Summary" (H6) only have the last range that has been updated showing. So if currently B4,D4 is showing in (H6) once the user types into B5,D5 then it replaces B4:D4. I would like this action to repeat itself until the user has completed their event. The reason I can not stick with a formula in (H6:H86) is because I have a macro running a copy.paste, then clear.contents once a selection is made in Column 4. The information must remain on the sub-sheets but it can be replaced in “Summary “. “Summary” is only a quick view of the current status while the sub sheets are a log of all the work done. So, the following code:

Code:

If Target.Column = 4 Then
Application.EnableEvents = False
If Target.Value = "Done" Then
Range(Target.Offset(0, -2), Target.Offset(0, 3)).Copy
Worksheets("Done").Range("D65536").End(xlUp).Offset(1, -2) _
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range(Target.Offset(0, -2), Target.Offset(0, 3)).ClearContents

            tabx = CStr(Target.Offset(0, 5).Value)
            
Worksheets("Done").Range("D65536").End(xlUp).Offset(0, 6).Value = _
 Sheets(tabx).Range("b3").Value & " " & Sheets(tabx).Range("c3").Value _
& " " & Sheets(tabx).Range("D3").Value
Worksheets("Done").Range("D65536").End(xlUp).Offset(0, 7).Value = _
Sheets(tabx).Range("b4").Value & " " & Sheets(tabx).Range("c4").Value _
& " " & Sheets(tabx).Range("D4").Value

allows the user to clear the row or Issue they are working on as well as the assigned Sheet by choosing DONE from a validation list. The data is stored in then store in sheet. "Done" for tracking and a simple audit if needed. I think the attached document will allow you a good idea of my described events and requirements.

I have been on this for 16 hours straight and was really hoping I could figure this one out on my own. To no avail.

Thank you all!

Panther1

**Panther1** · 11-25-2009, 11:48 AM

Finally was able to figure this thing out with help from jaslake. Just wanted to post if for the group. Target. Offset(0,8) is empty, and Target.Offset(0,7) is hidden with the sequence 1-14 starting in "I6". Formated a little different from my original sheet, but the procedure is still the same.

[CODE]If Target.Column = 2 Then
Application.EnableEvents = False
If Target.Value < 1 Then

Target.Offset(0, -1).Value = Target.Offset(0, 8)
Application.EnableEvents = True

Else

If Target.Value >= 1 Then

Target.Offset(0, -1).Value = Target.Offset(0, 7)
End If
Application.EnableEvents = True
End If
End If

End Sub[CODE]