Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
3  
your question is really unclear. We can't understand what you mean, what you intend to do. Show us what the inputs, your expected outputs and so on – JMax Mar 30 '12 at 18:41
    
I have updated some of the question. What I really need is a way to use a series of calculations as a single formula. Hope that this makes the question a bit more clear. – Георги Кременлиев Mar 31 '12 at 20:16
up vote 0 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
Not really what I was looking for. I tried to update the question with more details. – Георги Кременлиев Apr 2 '12 at 12:54

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .