Automate Goal Seek

I'm not exactly sure I understand what you are doing. I focused on this from your spreadsheet:

I can't figure out how to automate Goal Seek ("set S38 to 0 by change in cell Q9). While I can put a value into cell Q9 and run Goal Seek, when I let my formula put in a number, Goal Seek says Q9 must have value. But, I put in a value with my formula;

Q9 contains an IF(...) formula that chooses between 500 and another formula, depending on the year. As the error message from Goal Seek states, this is definitely a formula and not a constant, stand-alone value. An IF() formula that chooses between a constant and some other formula is still a formula.

What you need is to put the 500 in a cell all by itself, then have Goal Seek change that cell. What I did was:

1) Enter 500 into a convenient cell (I chose O2)
2) Edit the IF() functions in Q8:Q12 so that they refer to $O$2 (note the absolute reference) instead of the value 500.
3) Call Goal Seek and tell it to set S38 to a value of 0 by changing O2.

Goal Seek seemed to have no trouble finding a solution from there.

Did I understand correctly, or is there more to the question?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new, I have done it for you this time. Please read our rules.)

https://chandoo.org/forum/threads/au...al-seek.43096/

This help file should explain how to use Goal Seek: https://support.office.com/en-us/art...3-4f369d6e66c7

Here are the rules to which you said you agreed upon joining (as it's probably time you read them): https://www.excelforum.com/forum-rul...rum-rules.html

Which part is over your head? Someone over on the chandoo forum gave you an example VBA statement that will call goal seek, assuming that you would be able to provide the rest of the VBA context for that statement to run in. If you are unfamiliar with VBA in general, then maybe you want some generic VBA coaching or some examples of calling goal seek in VBA. Here's one example:
https://www.excelforum.com/excel-pro...-function.html

I often prefer Solver over Goal Seek, the algorithms seem more robust. If you want to use Solver here are some places to start:
https://peltiertech.com/Excel/SolverVBA.html
https://www.excelforum.com/excel-for...tool-pack.html

I would also mention that the spreadsheet looks like an amortization schedule, and I would not be surprised if it will reduce to a PV, NPV, or XNPV type problem. I don't know the financial models well enough to say how they might apply. If you understand the different financial models, the best approach might be to use Excel's built in functions to perform the amortization.