Hello,

I am having difficulties trying to build some complicated formulas, and
need help.

I have two spreadsheets in the same workbook – Sheet1 and Sheet2.

Sheet1
Range A1:S5 contains headings
Data range is A6:S50
Column F contains the name of a person
Column G contains ID numbers (formatted as General)
Columns H and N contain dates – Column H always has an earlier date

Sheet2
Range A1:G2 contains headings
Data range is A3:G1501

First, I added the following code to Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 8 And Len(Trim(Target.Value)) 0 Then
With Sheets("Sheet2")
NextRow = .Cells(65536, 3).End(xlUp).Row + 1
.Range("B" & NextRow & ":D" & NextRow).Value = Range("F" &
Target.Row & ":H" & Target.Row).Value
End With
End If
If Target.Column = 14 And Len(Trim(Target.Value)) 0 Then
With Sheets("Sheet2")
NextRow = .Cells(65536, 3).End(xlUp).Row + 1
.Range("B" & NextRow & ":C" & NextRow).Value = Range("F" &
Target.Row & ":G" & Target.Row).Value
.Range("E" & NextRow).Value = Range("N" & Target.Row).Value
End With
End If

End Sub

The problem is, every time I enter data in Sheet1 the code comes up
with the following line highlighted:

If Target.Column = 8 And Len(Trim(Target. Value)) 0 Then

(I know nothing about VB codes; I just copied it from a similar
spreadsheet and tried to modify it.)


This is what I am trying to achieve:

1. Whenever a date is entered in Column H of Sheet1, the contents of
Columns F, G and H of that row (of Sheet1) must be copied to Columns B,
C and D in the next empty row of Sheet2. Similarly, whenever a date is
entered in Column N of Sheet1, the contents of Columns F, G and N of
that row (of Sheet1) must be copied to Columns B, C and E in the next
empty row of Sheet2.

2. I want the total of Column G of Sheet2 entered in Column N of
Sheet1 but entered only for the first (or last) listing when there are
multiple listings having similar data in Columns C and D, e.g., C3 and
D3 are similar to C6 and D6. Similarly, I want the total of Columns G
of Sheet2 entered in Column Q of Sheet1 but entered only for the first
(or last) listing when there are multiple listings.

(I know that most of the other formulas are incorrect, but I am working
on them.)

Is there a way to attach a short portion of my spreadsheets?

Thanks,
Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=512052