Turn Excel spreadsheet into a formula [closed]

Question

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:

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

Answer 1

You might be able to do that with Excel's What-If Analysis. I'm not familiar enough with it to say. You can do it with a relatively simple macro.

At base, the macro would work like this:

Open the formula workbook
Set calculation to manual
Loop thru the rows you want to apply the formula to
   Take inputs on current row of result sheet and enter them in the formula workbook
   Force Excel to recalculate
   Take results of formula workbook and enter them in the result sheet
End loop
Turn calculation back on to automatic.

There are two ways to transfer the data in rows in your results workbook to the formula workbook in columns. One way is to copy & paste using Transpose to convert the row to a column, the other is to use offsets where for one range you are offsetting the column and the other range you are offsetting the row.

An example of the latter is as follows.

    ' set ranges to top left cell
    Set rngFrom = Workbooks("Result.xls").Worksheets("Sheet1").Cells(iCurrentRow, 1)
    Set rngTo = Workbooks("Formula.xls").Worksheets("Sheet1").Range("A1")

    ' transfer values
    For i = 0 To 9
        rngTo.Offset(RowOffset:=i).Value = rngFrom.Offset(ColumnOffset:=i).Value
    Next i

If you need specific help with writing a VBA macro, let us know.

Answer 2

I still think your question is too vague. It is hard to answer without any clear example. In your place I would try and find an excel expert who does the work for you. Someone with lots of expertise might be able to turn the whole thing into a macro.

I will attempt to give an answer, though, based on what I think I understood.

In most general terms, I think you just need to have the formulas in workbook 2 access the values in workbook 1 and vice versa. Linking the workbooks appears to be the most obvious solution. There are some lessons on Microsoft's website that show you how to do this (http://office.microsoft.com/en-us/excel-help/create-and-manage-links-to-other-workbooks-HA001054812.aspx, http://office.microsoft.com/en-us/excel-help/create-a-link-to-another-cell-workbook-or-program-HP005199514.aspx).

If you think my answer is way too off, either post an example or a screenshot or a video that show what you are trying to accomplish.

Answer 3

I agree, the question is a Little vague, and has 4 steps in the process. What step is it that is the most difficult? Can you make an example that is smaller and focus only on the part you can't manage?

1.Copy the values from A100...J100 into A1 to A10

The ranges are not the same size, what do you mean by copy? If you want a certain value in A1 then you just put =A100 in cell A1. You don't need to "copy" in fact, just "refer".

2.Wait for the result to appear in B1 to B5

You shouldn't need to really wait for this, but okay I guess it is doing some complex background stuff. I guess you have this part figured out.

3.Copy the values from B1 to B5 into K100 to O100 Again, if you want a value in K100 then you put =B1 into the cell K100 and it will "copy" it (refer to it)

4.Repeat steps 1 to 3 for all rows from 100 to 150 When you have set up row 100, then you will have a forumla in each cell. Now when you select the cell, there will be a small black "handle" in the bottom right corner. Click it and drag it down. The Formulas will change automatically to refer to cells either in an absolute way or a relative way. For example if you have some data in cell A1, and you want all cells A100 to A150 to "see" it, then you might put in cell A100: =$A$1 then cell A101 will also look at A1 and so on. So the $sign makes it an absolute reference.

Answer 4

I know this is quite late and I hope you were successful in your efforts.

I have create a few such spreadsheets. Some involved up to 14 lookup tables and a few dozen intermediate calculations and interpolations; all to produce a single table of numbers. This was based on several user inputs ranging from real numbers to drop down lists with lots of data validation. I used various lookups, index()/match() queries. And I did not use any scripting languages, as I don't particularly care for the continual warnings associated with macro-enabled spreadsheets. I created a number of sheets for the intermediate calculations and lookups, all protected with passwords and most were hidden. There was a data input sheet with unprotected cells and a results sheet.

Originally my hope was to hand it off to someone and it would walk the user through the necessary steps. Turns out that was too complex and required specific knowledge about certain topics.

In the end, I believe I invested over 120 hours and I used this as a tool for myself only. To make this work, I added one more sheet with a blank "template" formatted for copy & paste into a MS Word document. This has been working well for a number of years now. When some data in the lookup tables needed revision, I simply made those changes and the numbers reflect those changes right away.

Best, Doug