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 about 10 different inputs (values a human enters in different cells of the spreadsheet) and then it outputs 5 independent calculations (in 5 different cells) based on that input. Their calculation is using some pre-entered data in the spreadsheet (about 100 different constants) and doing some look-ups on them.
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:
- Copy the values from A100...J100 into A1 to A10
- Wait for the result to appear in B1 to B5
- Copy the values from B1 to B5 into K100 to O100
- Repeat steps 1 to 3 for all rows from 100 to 150