excel - Passing cells as input parameters of formula in other spreadsheet -

excel - Passing cells as input parameters of formula in other spreadsheet -

my workbook looks this:

sheet1: 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 =sumproduct(b2:b100, c2:c100, d2:d100)+c102/b102 sheet2: b c 1 parameter1 parameter2 finalresult 2 100 25 complicated formulas =if(sheet1!d102<1, 1, sheet1!d102) sheet3: b c 1 parameter1 parameter2 finalresult 2 110 30 ? 3 140 40 ? ...(about 200,000 rows)

more informaton: in both sheet1 , sheet2, actual formulas much more complicated ones shown here. in sheet1 have 154 intermediate variables calculated 7 basic parameters sheet2, sumproduct 1 intermediate function. in sheet2 there 15 different cases depending on parameters , index calculated sheet1. cannot hard code them , entrance in sheet2. reason wouldn't alter sheet1 , sheet2 because model bought other company , way told utilize input parameters in sheet2.

how can write formula in column c of sheet3 can calculate result every pair of parameter1 , parameter2? tried recording macro re-create , paste following:

sub toend() dim long application.screenupdating = false = 1 while < 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 = + 1 wend end sub

but in 6 seconds 200 results,i estimate time complexity 1 case 10^4 @ most, much slower expected. wonder if there's can written in c2 of sheet3 looks like

=passingparameterandgetreturnvalue(a2:b2, sheet2!$a$2:$b$2, sheet2!$c$2)

that can autofill inner loop hence faster? or other improve way task welcome.

updated answer:

i need function f looks f(x) {put x in right place of sheet2; wait till sheet2 finish calculation; result place of sheet2 , return}

=passingparameterandgetreturnvalue(a2:b2, sheet2!$a$2:$b$2, sheet2!$c$2)

your desired custom function (udf) requires changing values of other cells not possible because of excel environmental. way doing ("cells re-create & paste") run through vba sub procedure. may noticed not idea, cells re-create & paste performed on front-end screen updating, sightly slow. find more details in post: set cell value function.

as said before, no matter how many variables/formulas involved in sheet 1, sheet 2 redundant. separates calculation , increases workflow. sheet 2 input parameters dummy sheet 3. , can move sheet 2 formulas sheet 1 consistence.

if need custom function, way can modify worksheet construction next below steps posted earlier. or can write sub requires highly tailored coding go through every calculation workflow details. again, code/formulas can varied differently depended on actual case of scenario. cannot hard code here unless whole workbook.

i won't suggest utilize vba approach finish task (either coding sub or udf). far understand, task can solely completed normal worksheet functions. hope can hints below worksheet picture.

earlier answer:

actually, don't need vba , sheet 2. can result through 1 single formula:

basic worksheet formula function approach

i can see purpose of sheet2 single calculation (formula sheet2!d102). formula stored in sheet1 (formula sheet1!d102).

in fact, can combine 2 formulas in sheet1!d102 & sheet2!d102 1 single formula.

for example, in sheet3 cell c2, can utilize 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 simplified as:

=if( (sumproductresult+(parameter2/parameter1))<1 , 1 , (sumproductresult+(parameter2/parameter1)) )

logic & algorithm (not formula / code):

if (sumproductresult+(parameter2/parameter1)) < 1 show/return 1 else show/return (sumproductresult+(parameter2/parameter1)) end if vba approach

if want vba that, utilize vba create custom function (still using above algorithm).

function finalresultreturn(sumproductresultcell double, parameter1 double, parameter2 double) double computedvalued = sumproductresultcell + (parameter2 / parameter1) if computedvalued < 1 finalresultreturn = 1 else finalresultreturn = computedvalued end if end function how utilize vba: create vba module , paste above code creating new custom function. now can utilize 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 sumproductresultcell, i.e. =sumproduct(b2:b100, c2:c100, d2:d100). (ps: create sumproductresultcell minimizing input parameter of custom function.)

i hope didn't misinterpret worksheet calculation workflow. if yes, please allow me know , create modify.

excel vba