Passive / indirect formula calculation and data copy - without VBA?

[WayneBrady]

Hi Guys,

i just completed my registration as I guess you are playing the Championsleague of Excel, so I think it is the best place for a special question.

Think about a workbook having 2 sheets, the first sheet has multiple lines meaning multiple records with the same structure. The columns represent some parameters, while one column should show a calculated value.
Now comes the tricky part: The calculation of this value is so complex, that it has been put on an existing sheet (Sheet 2) using multiple references and formulas.

Would it be possible to do the following:

for each record/line in Sheet 1
copy value of the existing line in the fields in Sheet 2
copy the calculated value from sheet 2 into the column of the current record on Sheet 1
next record/line

without using VBA? So i thought about a formula which allows this "passive" calculation.

I hope this makes sense at all, if it is not understandable, please let me know.

Thank you for any hints!

 

[RasGhul]

Can you post some sample data, showing as well expect results so we can look at it for you?

 

[WayneBrady]

Hi,

sorry for the delay of my answer.

Lets assume the following structure (please consider that this is a simplified example, moving the formula from sheet 2 to sheet 1 is not possible, sheet 2 will be replaced from time to time but the structure will stay intact):

Sheet 1:

Sheet 2:

Assumptions:

Sheet 2 --> B1 is a input field
Sheet 2 --> B4 is a calculated field having a formula where the value of B1 is part of the calculation
Sheet 2 --> B5 is a calculated field having a formula where the value of B1 is part of the calculation

Goal: Sheet 1 Column E should have the value of Sheet 2 --> B4 for an input value of another column of each line.
Example: Sheet 1 --> E2 should show the value which is calculated in Sheet 2 --> B4 if Sheet 1 -->A2 would be put as input parameter on Sheet 2 --> B1.

Did my best to describe it, if it is still not clear, please let me know.

Thank you!