Passing cells as input parameters of formula in other spreadsheet

Question

My workbook looks like this:

Sheet1:
      A       B            C            D
1     VarName Multiplier1  Multiplier2  Multiplier3
2     Var1    0.3          0.8          0.4
3     Var2    0.4          0.9          0.1
...  
100   Var100  0.2          0.2          0.7
101           Parameter1   Parameter2   Index
102           Sheet2!A2    Sheet2!B2    Complicated formulas like =SUMPRODUCT(B2:B100, C2:C100, D2:D100)+C102/B102

Sheet2:
         A            B            C
1        Parameter1   Parameter2   FinalResult
2        100          25           Complicated formulas like =IF(Sheet1!D102<1, 1, Sheet1!D102)

Sheet3:
         A            B            C
1        Parameter1   Parameter2   FinalResult
2        110          30           ?
3        140          40           ?
...(about 200,000 rows)

More informaton: In Both Sheet1 and Sheet2, the actual formulas are much more complicated than the ones shown here. In Sheet1 we have 154 intermediate variables calculated from 7 basic parameters from Sheet2, the sumproduct is just one intermediate function. In Sheet2 there are 15 different cases depending on parameters and index calculated from Sheet1. So basically I cannot hard code them together and the only entrance is in Sheet2. Another reason I wouldn't change Sheet1 and Sheet2 is because this is a model bought from other company and the way we are told to use it is to input parameters in Sheet2.

How can I write formula in Column C of Sheet3 so that I can calculate the result for every pair of Parameter1 and Parameter2? I tried recording macro to copy and paste like the following:

Sub ToEnd()

Dim i As Long

Application.ScreenUpdating = False

i = 1

While i < 200000
    Worksheets("Sheet3").Range("A1:B1").Offset(i, 0).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("C2").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("C1").Offset(i, 0).Select
    Selection.PasteSpecial Paste:=xlValues
    i = i + 1
    Wend
End Sub

But in 6 seconds I only get 200 results,I estimate the time complexity for one case to be 10^4 at most, so it is much slower than what I expected. I wonder if there's something can be written in C2 of Sheet3 looks like

=PassingParameterAndGetReturnValue(A2:B2, Sheet2!$A$2:$B$2, Sheet2!$C$2)

that I can autofill with inner loop hence faster? Or any other better way to do my task would be welcome.

Answer 1

Updated Answer:

I need a function f looks like f(x) {put x in right place of Sheet2; wait till Sheet2 finish calculation; get the result from another place of Sheet2 and return}

=PassingParameterAndGetReturnValue(A2:B2, Sheet2!$A$2:$B$2, Sheet2!$C$2)

Your desired custom function (UDF) requires changing the values of other cells which is not possible because of the Excel environmental. The way you are doing ("cells copy & paste") is run through VBA Sub procedure. But you may also noticed that this is not good idea, as cells copy & paste are performed on front-end with screen updating, which is sightly slow. Find more details in this post: Set a cell value from a function.

As I said before, no matter how many variables/formulas are involved in sheet 1, your sheet 2 is redundant. It separates your calculation and increases your workflow. Sheet 2 input parameters obviously is dummy from sheet 3. And you can move sheet 2 formulas to sheet 1 for consistence.

If you really need a custom function, the only way you can do is modify you worksheet structure by following below steps I posted earlier. Or you can also write a Sub which requires highly tailored coding by go through every calculation workflow details. Again, the code/formulas can be varied differently depended on the actual case of your scenario. I cannot hard code here for you unless I get the whole workbook.

I won't suggest you use VBA approach to complete your task (either coding a Sub or UDF). As far as I understand, your task can be solely completed by normal worksheet functions. I hope you can get some hints from below worksheet picture.

---

Earlier Answer:

Actually, you don't need VBA and Sheet 2. You can get your result through one single formula:

Basic Worksheet Formula Function Approach

I can see that the purpose of your sheet2 is only for single calculation (formula Sheet2!D102). Another formula is stored in Sheet1 (formula Sheet1!D102).

In fact, you can combine your two formulas in Sheet1!D102 & Sheet2!D102 into one single formula.

For example, in Sheet3 cell C2, you can use this formula:

=IF((SUMPRODUCT(Sheet1!$B$2:$B$11,Sheet1!$C$2:$C$11,Sheet1!$D$2:$D$11)+(B2/A2))<1,1,(SUMPRODUCT(Sheet1!$B$2:$B$11,Sheet1!$C$2:$C$11,Sheet1!$D$2:$D$11)+(B2/A2)))

Above formula Logic can be simplified as:

=IF( (SumProductResult+(Parameter2/Parameter1))<1 , 1 , (SumProductResult+(Parameter2/Parameter1)) )

Logic & algorithm (not formula / code):

If (SumProductResult+(Parameter2/Parameter1)) < 1  
Then show/Return 1  
Else show/Return (SumProductResult+(Parameter2/Parameter1))  
End If 

---

VBA Approach

If you really want VBA to do that, then you use VBA to create custom function (still using above algorithm).

Function FinalResultReturn(SumProductResultCell As Double, Parameter1 As Double, Parameter2 As Double) As Double
    ComputedValued = SumProductResultCell + (Parameter2 / Parameter1)

    If ComputedValued < 1 Then
        FinalResultReturn = 1
    Else
        FinalResultReturn = ComputedValued
    End If
End Function

How to use this VBA:

1. Create a VBA module and paste above code for creating new custom function.
2. Now you can use your custom function in worksheet.

Sytnax:

=FinalResultReturn( SumProductResultCell , Parameter1 , Parameter2 )

Example:
In Sheet3 cell C2 input this:

=FinalResultReturn(Sheet1!$D$999,A3,B3)  

where Sheet1!$D$999 is the SumProductResultCell, i.e. =SUMPRODUCT(B2:B100, C2:C100, D2:D100).
(PS: I create this SumProductResultCell for minimizing the input parameter of custom function.)

I hope I didn't misinterpret your worksheet calculation workflow. If yes, please let me know and make modify.