I found 1 error. PNAME is a $%!&* (mind my french). Make sure you get the
right number of double quotes. There are single, double, and quadruple. I
never get this right the first time. Pname now refer to a name on the
worksheet.

The function ActiveWorkbook.Names("PName") put an equal sign in front of the
name which causes an error. It took me a while to figure out to use

ActiveWorkbook.Names("PName").RefersToRange.Formul a

which removes the equal sign. Hope this isn't too difficult for you to
understand.

the best way to learn the code is to single step through the code yourself.
Set a break point on the first line of code by left click on code then press
F9. Then enter you data on worksheet. When you get to a break point press
F8 to step through code. Highlighting a variable and right click lets you
add variable to watch window. Moving cursor over the code also makes
variable visable.

from:
MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C" & Target.Column
to:
MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C1"

This problem is causing another entry in S2 when changes are made.

I need to do the date formating if in the same cell you are combining a
string with the date. If I don't do this the date is treated as the number
of days from Jan 1, 1900 instead of date format.

To fix PNAME
from:
MyformulaColA = MyformulaColA & _
stringdate & "&"" PName"""
to:
LocalPName = ActiveWorkbook.Names("PName"). _
RefersToRange.Formula

MyformulaColA = MyformulaColA & _
stringdate & " & """ & LocalPName & """"

The new code should be as follows:


Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Target
If Not IsEmpty(Cells(Target.Row, "A")) And _
Not IsEmpty(Cells(Target.Row, "B")) And _
Not IsEmpty(Cells(Target.Row, "C")) Then

With Sheets("Sheet2")

If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C1"

'check if entry already exist
Found = False
For RowCount = 1 To (NewRow - 1)

cellref = .Cells(RowCount, "A").FormulaR1C1

beginofcellref = _
Left(cellref, Len(MyformulaColA))
If StrComp(MyformulaColA, _
beginofcellref) = 0 Then

Found = True
Exit For
End If
Next RowCount

If Found = False Then
stringdate = ", ""mm/dd/yy"")"
LocalPName = ActiveWorkbook.Names("PName"). _
RefersToRange.Formula

MyformulaColA = MyformulaColA & _
stringdate & " & """ & LocalPName & """"

.Cells(NewRow, "A").FormulaR1C1 = _
MyformulaColA
MyformulaColB = "=Sheet1!R" & _
Target.Row & "C2 * " & _
"Sheet1!R" & Target.Row & "C3"
.Cells(NewRow, "B").FormulaR1C1 = _
MyformulaColB
.Cells(NewRow, "B").NumberFormat = _
"$#,##0.00"

End If
End With
End If
Next cell
End Sub



"ssGuru" wrote:

On Jul 7, 7:16 pm, Joel wrote:
I've been really busy last couple of days. The code was a little tricky.
The check to make sure the row wasn't already on sheet 2 wasn't easy. I
don't know what you wanted for PName so this mnay need some corrections.

Sheet2 doesn't get an entry until columns A, B, & C all have entries in
sheet 1.

Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Target
If Not IsEmpty(Cells(Target.Row, "A")) And _
Not IsEmpty(Cells(Target.Row, "B")) And _
Not IsEmpty(Cells(Target.Row, "C")) Then

With Sheets("Sheet2")

If IsEmpty(.Range("A1")) Then
NewRow = 1
Else
NewRow = .Cells(Rows.Count, "A"). _
End(xlUp).Row + 1
End If

MyformulaColA = "=TEXT(Sheet1!R" & _
Target.Row & "C" & Target.Column

'check if entry already exist
Found = False
For RowCount = 1 To (NewRow - 1)

cellref = .Cells(RowCount, "A").FormulaR1C1

beginofcellref = _
Left(cellref, Len(MyformulaColA))
If StrComp(MyformulaColA, _
beginofcellref) = 0 Then

Found = True
Exit For
End If
Next RowCount

If Found = False Then
stringdate = ", ""mm/dd/yy"")"

MyformulaColA = MyformulaColA & _
stringdate & "&"" PName"""
.Cells(NewRow, "A").FormulaR1C1 = _
MyformulaColA
MyformulaColB = "=Sheet1!R" & _
Target.Row & "C2 * " & _
"Sheet1!R" & Target.Row & "C3"
.Cells(NewRow, "B").FormulaR1C1 = _
MyformulaColB
.Cells(NewRow, "B").NumberFormat = _
"$#,##0.00"

End If
End With
End If
Next cell
End Sub



"ssGuru" wrote:
On Jul 5, 12:06 pm, Joel wrote:
Sub Worksheet_Change(ByVal Target As Range)

Lastcolumn = Cells(Target.Row, Columns.Count). _
End(xlToLeft).Column

Set CopyRange = Range(Cells(Target.Row, "A"), _
Cells(Target.Row, Lastcolumn))

For Each cell In CopyRange
If Not IsEmpty(cell) Then
myformula = cell.Formula
myformula = "=Sheet1!" & Mid(myformula, 2)
Sheets("Sheet2").Cells(Target.Row, Target.Column). _
Formula = myformula
End If
Next cell
End Sub

Thanks Joel but the code now breaks at and doesn't add anything to SS2
"... Sheets("Sheet2").Cells(Target.Row, Target.Column). _
Formula = myformula
...."

For example in SS1 I might have headings and data such as:
PipeDate ProLic LicCost
07/05/07 4 $100.00
07/03/07 3 $200.00

In SS2 which ONLY has formulas I might have headings and formulas such
as:
SalesRecID LicTotalGross
=IF(Sheet1!A2<"",Sheet1!A2&"PName","") =IF(A2<"",(Sheet1!
B2*Sheet1!C2),"")
=IF(Sheet1!A3<"",Sheet1!A3&"PName","") =IF(A3<"",(Sheet1!
B3*Sheet1!C3),"")

Records in SS1 will be bulk loaded from another template. There may
be one or many.

My goal again is when NEW record(s) are added to SS1 that code then
copies and paste/special/formula or INSERT the formulas(example) from
the last row in SS2 (or from code) to the same number of blank rows in
SS2 that I have new records just added to SS1. Of course I need to
keep the number of records in SS2 in sync with the number of records
in SS1.

ALSO do you think that the best approach to keeping the number of
records in SS1 and SS2 in sync is to use an onchange for SS1 which
fires with each change in each cell? In addition to new records being
added, some existing records in SS1 may be periodically updated with
different values and it would not be necessary to change any formulas
for that corresponding row in SS2.

I think I would like to keep the calculating formulas for SS2 in code
rather than copying from previous last row of SS2 if possible.

Thanks again for your consideration and help,
Dennis- Hide quoted text -

- Show quoted text -

Thanks Joel for all your time and expertise. I thought I had posted a
response but don't see it so posting another.
The sample code does create records on S2 after entering values in S1
so thanks again for giving me a start on this project.

Still more than a couple of problems I need to resolve however. I'm
apparently too dense to find where in the code that a resultant S2
ColA formula is forced to point to the correct column in S1. In the
sample it needs to point to S1 ColA or to the NAMED range DateRec
which are the same thing.

The formulas created in S2 ColA by the code have incorrectly referred
to S1 ColC instead of S1 ColA.
Example: =TEXT(Sheet1!$C$2, "mm/dd/yy")&" PName"
=TEXT(Sheet1!$C$3, "mm/dd/yy")&" PName"
(BTW PName should refer to a Named cell to return a company name and
the date should just return the serial value so date formatting NOT
needed.)
The result value tries to use the cost of a license from ColC instead
of the date of the record ColA from S1 so it fails to return a correct
value.

ALSO
ANY change to any existing record fields and the code fires off
another record entry in S2 rather than the number of records in S1 and
S2 being the same count.

ALSO any deletion of a record in S1 means that the corresponding
record in S2 returns the #REF error since its reference cells are
gone.

I'm sorry but I have not been able to define where in the code that
ColC is obtained instead of ColA. Thanks for showing me this can be
done at least even though I'm still struggling. The calculations for
S2 Colb are working perfectly since they point to the correct S1
columns to multiply them together.

All the columns in my real project are NAMED ranges so my formulas in
S2 refer to the NAMED ranges in S1 and return the values on the SAME
row between the two sheets. Most are fairly complex but work like a
charm. So if you were to examine ANY record in S2 the formulas would
look the same as any other record in S2. Deleting or editing records
in S1 has no effect on S2. It just keeps happily reporting on its
row. The problem I needed to solve was that in order to maintain this
project I have to remember to copy and maintain the same number of
rows in S2 as I have in S1 for some complex calculations and counts to
work. I felt the ability to programmatically maintain the formulas
needed for each record by NAMED column range in S2 and add or remove
S2 records as needed to match the count in S1 would be very useful.
It is proving to be a greater challenge than it seems that it should
be.

I feel that maintaining separate DATA and CALCULATION spreadsheets is
good practice and allows more accurate importation and editing of the
DATA without incurring any problems with any calculations. I was
surprised that we didn't see more feedback on this issue to help us
resolve the problems.
Again Joel, thanks for your valuable time and expertise. If you can
help point me to resolving the sample code problems that would be much
appreciated. If you can help point me on how to use NAMED ranges
rather than just RC references in this sample automation code that
would be great.

Dennis