I have a structured SS1 without any formulas. Header row and records.
I have a SS 2 that does all the calculations needed to report on SS1.
Its formulas point to SS1.

How can I have code automatically add formulas after the previous last
row in SS2 when a new record is added to SS1?

Currently I manually copy these IF THEN formulas for about 500 empty
records at a time in SS2 in preparation to receive new records in
SS1. They return "" if no values in SS1.

Is it possible to automate this process one record at a time?

Thanks for your feedback,
Dennis

Using a worksheet_change macro on sheet1 you can add formulas into sheet2.

"ssGuru" wrote:

I have a structured SS1 without any formulas. Header row and records.
I have a SS 2 that does all the calculations needed to report on SS1.
Its formulas point to SS1.

How can I have code automatically add formulas after the previous last
row in SS2 when a new record is added to SS1?

Currently I manually copy these IF THEN formulas for about 500 empty
records at a time in SS2 in preparation to receive new records in
SS1. They return "" if no values in SS1.

Is it possible to automate this process one record at a time?

Thanks for your feedback,
Dennis

On Jul 4, 12:04 am, Joel wrote:
Using a worksheet_change macro on sheet1 you can add formulas into sheet2.



"ssGuru" wrote:
I have a structured SS1 without any formulas. Header row and records.
I have a SS 2 that does all the calculations needed to report on SS1.
Its formulas point to SS1.

How can I have code automatically add formulas after the previous last
row in SS2 when a new record is added to SS1?

Currently I manually copy these IF THEN formulas for about 500 empty
records at a time in SS2 in preparation to receive new records in
SS1. They return "" if no values in SS1.

Is it possible to automate this process one record at a time?

Thanks for your feedback,
Dennis- Hide quoted text -

- Show quoted text -

Could you give a brief code example or point me to a site for
something to work with?
Dennis

Put the following code in a new workbook as follows

1) Go to sheet 1
2) right click tab on bottom of worksheet that is labeled Sheet1
3) Select last item "View Code"
4) Copy and Past the Macro below into the VBA sheet.


Now anything you type on sheet 1 will get referenced on sheet 2 with a
formula.


Sub Worksheet_Change(ByVal Target As Range)

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

.Cells(NewRow, "A").FormulaR1C1 = _
"='Sheet1'!R" & Target.Row & "C" & _
Target.Column
End With
End If


"ssGuru" wrote:

On Jul 4, 12:04 am, Joel wrote:
Using a worksheet_change macro on sheet1 you can add formulas into sheet2.



"ssGuru" wrote:
I have a structured SS1 without any formulas. Header row and records.
I have a SS 2 that does all the calculations needed to report on SS1.
Its formulas point to SS1.

How can I have code automatically add formulas after the previous last
row in SS2 when a new record is added to SS1?

Currently I manually copy these IF THEN formulas for about 500 empty
records at a time in SS2 in preparation to receive new records in
SS1. They return "" if no values in SS1.

Is it possible to automate this process one record at a time?

Thanks for your feedback,
Dennis- Hide quoted text -

- Show quoted text -

Could you give a brief code example or point me to a site for
something to work with?
Dennis

On Jul 4, 6:56 pm, Joel wrote:
Put the following code in a new workbook as follows

1) Go to sheet 1
2) right click tab on bottom of worksheet that is labeled Sheet1
3) Select last item "View Code"
4) Copy and Past the Macro below into the VBA sheet.

Now anything you type on sheet 1 will get referenced on sheet 2 with a
formula.

Sub Worksheet_Change(ByVal Target As Range)

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

.Cells(NewRow, "A").FormulaR1C1 = _
"='Sheet1'!R" & Target.Row & "C" & _
Target.Column
End With
End If



"ssGuru" wrote:
On Jul 4, 12:04 am, Joel wrote:
Using a worksheet_change macro on sheet1 you can add formulas into sheet2.

"ssGuru" wrote:
I have a structured SS1 without any formulas. Header row and records.
I have a SS 2 that does all the calculations needed to report on SS1.
Its formulas point to SS1.

How can I have code automatically add formulas after the previous last
row in SS2 when a new record is added to SS1?

Currently I manually copy these IF THEN formulas for about 500 empty
records at a time in SS2 in preparation to receive new records in
SS1. They return "" if no values in SS1.

Is it possible to automate this process one record at a time?

Thanks for your feedback,
Dennis- Hide quoted text -

- Show quoted text -

Could you give a brief code example or point me to a site for
something to work with?
Dennis- Hide quoted text -

- Show quoted text -

Thanks for your excellent example Joel.
This gives me a place to start at least. The result of your example
is that as Values are added to Sheet1, Col1, Col2, Col3... they are
automatically referenced for example down ColA as:
=Sheet1!$A$1
=Sheet1!$B$1
=Sheet1!$C$1
=Sheet1!$A$2
=Sheet1!$B$2
=Sheet1!$C$2
=Sheet1!$B$3
=Sheet1!$B$4

My goal is to replicate some calculation formulas I already have in
sheet2 across some columns that do reference the same row in Sheet1 by
using Named Column/Fields. When a new record is added to Sheet1 Row 5
for example I want to replicate all the column formulas I already have
in Sheet2 Row 4 and copy them down to Sheet2 Row5.

For example a formula I now have manually copied down a column in
Sheet2 may be something like:
=IF(ProspectRetail<"",VLOOKUP(ProspectRetail,TblD iscount,3,TRUE),"")
The result is for example this formula references a column in Sheet1
named "ProspectRetail" in the same row as this formula, looks up that
$value amount in a table named "TblDiscount" and returns the discount
rate from column3 of TblDiscount. If there is NO value in Sheet1 it
returns a "". I have many similar formulas in Sheet2 copied down a
fixed number of rows which works just fine. UNTIL at some future time
I have more records in Sheet1 than I have already copied formulas in
to Sheet2.

Maybe for every new record created in Sheet1 by adding a value in ANY
column of a new record, Worksheet_Change can just trigger a paste of a
predetermined set of formulas into Sheet2 in the first empty row by
counting the existing rows. Perhaps the formulas can be held in code
OR I can copy them from the last row currently in Sheet2 with formulas
and PASTE/SPECIAL/FORMULAS into the entire new row in Sheet2 that now
needs formulas because of a new record in Sheet1.

Can you suggest a better method or any help with the Worksheet_Change
code in Sheet1 that will force it to copy the formulas I already have
to the corresponding row(s) in Sheet2?

Thanks again for your excellent suggestions and help,
Dennis

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