Paste-Link Problem

S

Shanei

I have constructed a calculator in a spreadsheet that will calculate breach flow rate given values inputed by the user (Sheet 1). There is also a seperate spreadsheet within the same workbook that contains numourus different site numbers and their location (Sheet 2). I am wanting to include the calculated breach flow from sheet 1 into sheet 2 for each site. I have used the copy and paste link combo and it works for the first site but when I go and enter the data for the second site in the calculator it also changes my calculated value for the first site in sheet 2. Is there an IF statement that I need to include in sheet 2 to prohibiting the live update of the above cells? I hope that I explained this well enough. If need any more info let me know. Thanks in advance for your help

Shane Ic
[email protected]
 
D

Dave Peterson

I don't think you're going to be able to do this using formulas.

But you could have a macro that takes the values in Sheet1 (one at a time) and
populates sheet2 (the calculation engine).

After the calculation is done, then the value from a cell (or a bunch of cells)
is copied back to sheet1.

So you'd have your input cells in A:G (say) and your output cells in H:K (say).

You'd populate all the input cells (10000 rows!) and then run the macro to do
the calculation for each iteration.

This example takes 3 values (A:C of each row), puts them in 3 cells in sheet2,
calculates and returns two values to the sheet1.

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
.Range("a1").Value = myCell.Value
.Range("b3").Value = myCell.Offset(0, 1).Value
.Range("c9").Value = myCell.Offset(0, 2).Value
Application.Calculate
myCell.Offset(0, 9).Value = .Range("g10").Value
myCell.Offset(0, 10).Value = .Range("x99").Value
Next myCell
End With

End Sub
 
Top