Names and Separate Worksheets

[Guest]

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.

 

[Roger Govier]

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

 

[Guest]

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,

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

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.

 

[David Biddulph]

Excel doesn't name a formula, it names a cell or a range of cells.

Having changed your formula from absolute to relative references, copy it
down as far as you need it, and the row number in the references will update
accordingly.
--
David Biddulph

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,

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

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.

 

[Roger Govier]

Hi

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.

apologies, I meant to wrap that in a Sum()
=SUM([worksheet1.xls]Sheet1!$A1:$D1)

I still don't understand why you are getting the same values from A1+B1
when you copy down.
The columns are absolute, but the rows are relative and should adjust to
A2+B2, A3+B3 as you copy down the range.

If you want, send me a copy of your workbook direct.
To send direct remove NOSPAM from my email address.
--
Regards

Roger Govier

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,

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

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.