Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.