Some really good threads here but couldn't find what I am looking for.
My formula is very complex on 1 spreadsheet (sheet). There are 6 variables to input that eventually calculates a single answer. I want to change the variables to compare the outputs (answers) for investing decisions. Now the problem is that since the formula is so long and complex, it is impractical to keep copying and pasting the formula then syncing each variable. Is there a way to use the 1 formula like some kind of "Master" formula and run through many different variables?
Here's a short and clear example.
Spreadsheet 1 or sheet 1- containing complex formula
input a input b input c input d input e input f answer = input a + input b + input c + input d + input e + input f
Spreadsheet 2 or sheet 2 - spreadsheet with variables
input a = 1 input a = 1 input a = 1 input b = 1 input b = 1 input b = 1 input c = 0 input c = 1 input c = 1 input d = 0 input d = 0 input d = 1 input e = 0 input e = 0 input e = 0 input f = 0 input f = 0 input f = 0
This is the input/variables page. By using spreadsheet 1 and plugging the inputs from spreadsheet 2 to spreadsheet 1, the
answer = 1 + 1 + 0 + 0 + 0 + 0 = 2. Easy. But now, the challenge is, how can I input the next set of inputs into spreadsheet 1 without affecting the answer I just got in spreadsheet 1? If we continue, the next inputs from spreadsheet 2 are
1 + 1 + 1 + 0 + 0 + 0 after which spreadsheet 1 calculates to 3.
Is there someway to reference the formula for every calculation so that it's not fixed? Maybe I'm asking the wrong questions. I'm just trying to use this single complex formula by using many inputs to calculate multiple answers that can be analyzed. Any ideas?
Thanks so much!