spurs
Active Member
- Joined
- Oct 18, 2006
- Messages
- 479
- Office Version
- 2016
- 2013
- 2010
- 2007
- 2003 or older
- Platform
- Windows
I have never tried this before but ran into a roadblock.
I have a macro that runs every time my worksheet is updated. The macro makes about 2 dozen calculations which are output to cells in a worksheet named "Holder"
One cell of interest is calculated by this macro and is output to cell "B4" in worksheet "Holder".
In the worksheet "Main" cell "C23" is set to =Holder!B4
Also in worksheet "Main" is an input value in cell "C20" This value is read into the macro as the basis of the 2 dozen calculations that are done.
Sometime I want to do a What-if-analysis - goal seek to set the value in "Main" C23 to some predetermined value by changing input cell C20 in main.
In doing so, goal seek gets lost and does not find a solution.
In the past I have done a similar thing using a function in C23 and using goal seek works fine. The problem is that I really need to do the 12 different calculations as opposed to a single calculation.
Is the method the problem of why a solution wont converge? Is there a solution that will allow me to continue to use the macro with the 2 dozen calculations?
I have a macro that runs every time my worksheet is updated. The macro makes about 2 dozen calculations which are output to cells in a worksheet named "Holder"
One cell of interest is calculated by this macro and is output to cell "B4" in worksheet "Holder".
In the worksheet "Main" cell "C23" is set to =Holder!B4
Also in worksheet "Main" is an input value in cell "C20" This value is read into the macro as the basis of the 2 dozen calculations that are done.
Sometime I want to do a What-if-analysis - goal seek to set the value in "Main" C23 to some predetermined value by changing input cell C20 in main.
In doing so, goal seek gets lost and does not find a solution.
In the past I have done a similar thing using a function in C23 and using goal seek works fine. The problem is that I really need to do the 12 different calculations as opposed to a single calculation.
Is the method the problem of why a solution wont converge? Is there a solution that will allow me to continue to use the macro with the 2 dozen calculations?
