M
Memento
Okay folks,
This is tearing me apart:
I am trying to put my 'engine - the thing that does al the calculations'
into a separate worksheet. I have fairly advanced formulas, so i will
illustrate my problem with a short example:
This is the input in worksheet 1 (saved as worksheet1.xls):
A B C D E (SUM)
1 25 54 79
2 46 87 34 163
3 67 44 33 143
This is the 'engine' (saved as worksheet2.xls):
=worksheet1.xls!A1+B1+C1+D1 (aka named formula "TestFormula")
If i go to my worksheet1.xls, and i refer to "worksheet2.xls!TestFormula",
it gives me the correct values in cell A1, but if I try to copy the formula
downwards, it doesnt work anymore: so cell A2 would give me the result in
A1.... Instead of refering to the values of A2+B2+C2+D2, it keeps on using
the formula as the is noted in worksheet2.xls.
Is there any way to circumvend this kind of behaviour with parameters or
something likewise? Or an easier way.
I am aware i could copy the entire range into worksheet1.xls, give it names,
and use the names eventually in worksheet1.xls. But this is just what i would
like to avoid, i am trying to keep everything as compact as possible.
This is tearing me apart:
I am trying to put my 'engine - the thing that does al the calculations'
into a separate worksheet. I have fairly advanced formulas, so i will
illustrate my problem with a short example:
This is the input in worksheet 1 (saved as worksheet1.xls):
A B C D E (SUM)
1 25 54 79
2 46 87 34 163
3 67 44 33 143
This is the 'engine' (saved as worksheet2.xls):
=worksheet1.xls!A1+B1+C1+D1 (aka named formula "TestFormula")
If i go to my worksheet1.xls, and i refer to "worksheet2.xls!TestFormula",
it gives me the correct values in cell A1, but if I try to copy the formula
downwards, it doesnt work anymore: so cell A2 would give me the result in
A1.... Instead of refering to the values of A2+B2+C2+D2, it keeps on using
the formula as the is noted in worksheet2.xls.
Is there any way to circumvend this kind of behaviour with parameters or
something likewise? Or an easier way.
I am aware i could copy the entire range into worksheet1.xls, give it names,
and use the names eventually in worksheet1.xls. But this is just what i would
like to avoid, i am trying to keep everything as compact as possible.