Thanks already Roger for your directions.

So far i've been experimenting with a the reference types, but it keeps

giving me the value of A1 + B1, instead of A2 +B2, etc.. when i copy the

named formula downwards...

So the following formula:

=[Map2.xls]Sheet1!$A$1+[Map2.xls]Sheet1!$B$1

simply won't work with running totals just because i'm using absolute

references if I understand correctly. So i've changed this one into:

=[Map2.xls]Sheet1!$A1+[Map2.xls]Sheet1!$B1

And I named this formula "FormulaTest"

In the cell where the value needs to appear in Map1.xls, i've used:

=Map2.xls!FormulaTest

In case of A1 + B1 this gives me the wanted result, but if I copy down, it

keeps giving me the values of A1 +B1...

I must be misunderstanding something in your description Roger. I am kinda

confused about your last sentence: "but rather than the long formula then

=[worksheet1.xls]Sheet1!$A1:$D1.

This confuses me, because I cannot see where you want to go... Can you

clarify this with a less confusing one

Maybe I'm just stupid, that's

also

a possibility offcourse

No, just kidding.

I understand your reply where you say i'm seem to be using the wrong kind

of

references (absolute vs relative), but I can't see what I'm doing wrong

here.

Thanks already, and I won't give up on this one.

Regards,

Roger Govier said:

Hi

The problem here

=worksheet1.xls!A1+B1+C1+D1

is that it will be taking A1 from Worksheet1, but B1, C1 and D1 from

Worksheet2.xls

You would need

=worksheet1.xls!A1+worksheet1.xls!B1+worksheet1.xls!C1+worksheet1.xls!D1

You should be including the sheet name as well, and making the column

absolute

=[worksheet1.xls]Sheet1!$A1 etc

but rather than the long formula

then

=[worksheet1.xls]Sheet1!$A1:$D1

If you are getting the value from A1 as you copy down, then it sounds

like you have made A1 absolute as $A$1

--

Regards

Roger Govier

Memento said:

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.