how to insert formula for goal sick applicable for changing values from table in exel

The following problems happens -- how to resolve this

With this formula following problems happens

Please advice

The specific error in the screenshot is because the formula I previously gave you contained the text “[calculation using mid]”. That was a placeholder, not a valid Excel expression, so Excel correctly reports “The syntax of this name isn't correct.” More importantly, there is a fundamental limitation here: a normal worksheet formula cannot change D9 based on the calculated value of E11, because E11 already depends on D9. Putting a formula in D9 that tries to make E11 equal E10 creates a circular dependency. Goal Seek can do this because it is an external calculation operation rather than an ordinary cell formula.

Since you have 600 sheets and want the equivalent of Goal Seek to happen automatically, the most practical solution is VBA. It can use Excel's existing Goal Seek functionality directly, so you do not need to rewrite the E11 calculation. Put the following code in ThisWorkbook in the VBA editor:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Sh.Range("E19:E616")) Is Nothing Then Exit Sub

On Error GoTo CleanUp
Application.EnableEvents = False

Sh.Range("E11").GoalSeek _
    Goal:=Sh.Range("E10").Value, _
    ChangingCell:=Sh.Range("D9")
CleanUp: Application.EnableEvents = True

End Sub

With this in place, whenever a value in E19:E616 is changed on any of your 600 worksheets, Excel automatically performs the equivalent of Data → What-If Analysis → Goal Seek, using E11 as the formula to solve, E10 as the target value, and D9 as the changing value. Thus, if E10 contains 0.5, Excel will automatically adjust D9 until E11 reaches 0.5.

This approach is preferable to the LET/REDUCE formula for your situation because it does not require us to duplicate the calculation contained in E11. It simply tells Excel: “Use the existing E11 calculation, change D9, and make E11 equal E10.” The same code applies to all 600 sheets, provided they use the same cell arrangement.

One limitation is that Workbook_SheetChange responds when E19:E616 is manually changed or changed by another macro. If those table values are themselves formulas whose results change because of changes elsewhere in the workbook, use a different event mechanism so the Goal Seek runs after recalculation rather than after a direct cell change.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Hello Macrin -- Greetings I am Total a mechnaical engineer, have logic but like Software engineers..

I have open the VBA --> Macro as shown below.. error is happening.. How to solve it please advice..

The error is happening because, as shown in your screenshot, you have a “Sub run()” before the “Private Sub Workbook_SheetChange(...)”. That means VBA thinks you are trying to put one procedure inside another procedure, which is not allowed.

The “Workbook_SheetChange” procedure must be placed in “ThisWorkbook”, not in “Module1”. In the VBA editor, under “Microsoft Excel Objects”, double-click “ThisWorkbook”. Delete anything currently there that you do not need, and paste the following code there:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Intersect(Target, Sh.Range("E19:E616")) Is Nothing Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False

    Sh.Range("E11").GoalSeek _
        Goal:=Sh.Range("E10").Value, _
        ChangingCell:=Sh.Range("D9")

CleanUp:
    Application.EnableEvents = True

End Sub

Then go to “Module1” and remove the “Sub run()” and the “Workbook_SheetChange” code from there. You do not need anything in Module1 for this solution.

Save the workbook as an “.xlsm” file and return to Excel. Now, whenever a value in E19:E616 is manually changed on any of your worksheets, the event will automatically execute Goal Seek on that particular sheet: it uses E11 as the cell to set, E10 as the target value, and D9 as the cell to change. If if E10 is 0.5, Excel will automatically adjust D9 until E11 reaches 0.5.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Hello Marcin -- I am sorry to disturb you again -- Kindly have bit pateince in this Case..

I did the following process as you told me .. But not happneing in D9.

Excel will automatically adjust D9 until E11 reaches 0.5. -- > this does not happens.

Do i have press any tab like RUN to execute it

Or

it will happen automatically. (as you said it will happen automatically)

Marcin