Multiple results in sheet 1, one (complex) formula in sheet 2

**janroger** · 11-21-2008, 03:25 AM

I have a workbook with two sheets. In the first sheet, I have 3 variables and 1 result cell for each column. The 3 variables are input to the second sheet, which does some complex calculations (10 different formulas, with several lookup functions). The output from the second sheet is in one single cell. I want to copy this result to the result cell in the first sheet for each column.

This seems like a simple problem, and might have a simple solution for all I know. But I have no idea of where to start looking. If the formulas in the second sheet where simple, I would of course just copy the formula into the result cell in the first sheet, but they are way to complex to do that.

Using Excel 2007

Regards,
janroger

**davesexcel** · 11-21-2008, 03:32 AM

Unclear of the question, can you not just refer to the result cell, =sheet1!f1 or whatever.
If you do attach a sample workbook, please save it as a 97-'03 .xls version.

**janroger** · 11-21-2008, 03:54 AM

Thanks for replying,

That is ok for the first column (the first result). But for the rest, I would have to change the formula in the second sheet. (The second sheet are referring to the input variables in the first sheet).

See attached Excel (97-03) -file.

**EdMac** · 11-21-2008, 07:14 AM

Clear as mud - I have no idea what you want to achieve. Just putting ??? on the sheet does not explain what you want to achieve or where the information is coming from.

**janroger** · 11-21-2008, 07:41 AM

Hi, and thank you for your reply..

I'll try again...

I have a Sheet (Sheet2 in my example) that is realtively complex. The input to this sheet is 3 variables. The output is one single variable. Now, I have about 200 different datasets (3 variables, all different for the 200 datasets) listed in Sheet1. This would result in 200 computations. I would like to list the result from all these computations in Sheet1 (see example attached).

Doing it manually would work like this: In Sheet2 I would refer to Dataset1 in Sheet1, take the result from Sheet2, and paste it into Sheet1's cell B6. Then I would go back to Sheet2, and do the same thing for Dataset2: change the reference to Sheet1's Dataset2... all the way to Dataset200.

How can I automate this? If this was just some simple calculations, I would just type in the formula in Sheet1!B6, and copy that formula to Sheet1!C6:GR8, but the computation done in Sheet2 is far to complex to do that.

Hope this helps...

Regards,
janroger

**EdMac** · 11-21-2008, 10:23 AM

I don't understand why you have to keep going from one sheet to another.

you say

[QUOTEIf this was just some simple calculations, I would just type in the formula in Sheet1!B6, and copy that formula to Sheet1!C6:GR8, but the computation done in Sheet2 is far to complex to do that.[/QUOTE]

Simple or complex, it's all the same in excel.

You seem to have decided how you would like to solve the problem but it is often better to define the problem in detail clearly and others may then come up with a better solution or range of possible solution.

I still can't work out what you are seeking to achieve.

**janroger** · 11-21-2008, 12:19 PM

What I have is a excel file to calculate weights of hydropower turbines.The calculations are done in sections. A section is defined as a part of the turbine, such as the shaft and the runner. Each section is calculated separatly, and for each section there is a separate sheet in this excel file calculating its weight. (This is the way the engineers like it ;-)

A hydropower turbine vary in size based upon head, flow, speed, power etc. These sheets take these variables as parameters, and calculates the weights - one sheet = one component = several parameters = one result (weight).

What I would like to do is to list a bunch of parameters, starting with a blank spreadsheet. For turbine 1, I would type in the parameters in column A, B, C, D. I would then do the same thing for turbine 2 on the next row. Next to the last parameter-column, column E, I would like the weight of the shaft (one of the sections). (Row 1, column E = weight of the shaft for turbine 1.) What I would like Excel to do for me is to take the parameters for turbine 1, pass them on to the sheet that calculates the weight for the shaft, do the calculation in that sheet, and pass on the result back to sheet 1, row 1, column E. Then it should do the same with parameters for turbine 2, 3 etc.. The end result would be the shaft weight for several different turbines listed in column E, next to their parameters.

I could re-write the excel sheets that calculates the weights, but then I would have to do that every time the engineers change something. That is why I'm not very eager to do just that. If there are no built-in Excel functions that can help me accomplish what I need, I can allways try doing this with VBA. I was actually looking into User Defined Functions, but I realized they cannot alter the content of another cell, other than the cell which is referring to the function.

Sorry i I might have confused you with my simplified example, but I simply cannot post the "real stuff" here, since it contains proprietary information.

Regards,
janroger

**EdMac** · 11-24-2008, 11:46 AM

Hi again,

What I would like to do is to list a bunch of parameters, starting with a blank spreadsheet. For turbine 1, I would type in the parameters in column A, B, C, D. I would then do the same thing for turbine 2 on the next row. Next to the last parameter-column, column E, I would like the weight of the shaft (one of the sections). (Row 1, column E = weight of the shaft for turbine 1.) What I would like Excel to do for me is to take the parameters for turbine 1, pass them on to the sheet that calculates the weight for the shaft, do the calculation in that sheet, and pass on the result back to sheet 1, row 1, column E. Then it should do the same with parameters for turbine 2, 3 etc.. The end result would be the shaft weight for several different turbines listed in column E, next to their parameters.

From what you have said, you appear to be wanting a calculation that takes the 4 parameters and calculates the resulting weight - this result to appear in col E.

From this description the calculation could just as easily be carried out in Col E as in another sheet, although it may need to reference some data held elsewhere.

The only exception to this would be if you wanted to manually enter a figure in Col E, do some calculations using this figure and then overwrite the manually entered figure - you would need VBA to do this.

Without having some real data and the detail of the calculations that are needed, I don't think I can help further.

HTH

Multiple results in sheet 1, one (complex) formula in sheet 2

LinkBack

Thread Tools

Rate This Thread

Display

Multiple results in sheet 1, one (complex) formula in sheet 2

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions