Turn Excel spreadsheet into a formula
- by ?????? ??????????
I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has a about 10 different inputs (values a human enters in different cells of the spreadsheet) and then it outputs 5 independent calculations (in different 5 cells) based on that input. There calculation is using some pre-entered data in the spreadsheet (about 100 different constants) and doing some look-ups on them.
Now I would like to use this whole spreadsheet as a formula on a different spreadsheet to calculate a set of input values and produce the corresponding set of output values. Imagine this as creating different table with 10 columns for the input variables and 5 columns for the outputs, then copying each input into the other spreadsheet and copying back the output in the results table.
For instance:
- A1, A2, A3,... A10 are cells where someone enters values
- through a series of calculations B1, B2, B3, B4 and B5 are updated with some formulas
Can I use the whole series of calculations from A1..A10 into B1..B5 without creating one massive huge formula or a VBA macro?
I want to have a set of input values in 100 rows from A100, B100, C100,... J100 onward. Then do some Excel magic that will:
1. copy the values from A100...J100 into A1 to A10
2. wait for the result to appear in B1 to B5
3. copy the values from B1 to B5 into K100 to O100
4. repeat steps 1 to 3 for all rows from 100 to 150
