Copy Formulas from One Table to Another

[TKS_Mark]

When copying formulas from one table to another, these formulas should refer
to data in the table they're being copied to. But instead, the formulas look
over their shoulders to where they came from. See the example:

Table named Table1.
Name Width Length Area
Floor 12 15 =[Width]*[Length]

If you just copy the lower right formula to a new table named Table2, you get
Name Width Length Area
Floor 20 19 =Table1[Width]*Table1[Length]

Now in Table2, it doesn't matter what you type in for length and width
because the formula is looking at Table1. Why can't the copy command know
that the destination location has those same column labels and use those
instead of looking over it's shoulder? Excel has always looked forward when
you paste formulas. A1*A2 will stay the same when you go from one sheet to
another. If you screw up and try to put your formula in a position that
can't have an A1*A2, it will change to REF!. That makes it obvious that you
have an error.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Bernard Liengme]

Because you used named cells. Had you used cell references (like B4 ) it
would work.
Named cells are designed to work that way and many of us use them that way.
So it I was anywhere where the last part of you message had any meaning, I
would mark "Not agreed"

However, all is not lost. If you use Name | Define and see that a cell
reference such as =Sheet1$A$1. Then remove the $s and the name will be
relative not absolute. I find this use of names very confusing but that
could be old age
best wishes

 

[TKS_Mark]

This isn't a named cell. It is a new feature of Excel 2007 called tables.
The column heads of tables can be used as formula references. So if someone
edits a column heading that said Length and changed that to Height, the
formula that referred to it would also change.

Because you used named cells. Had you used cell references (like B4 ) it
would work.
Named cells are designed to work that way and many of us use them that way.
So it I was anywhere where the last part of you message had any meaning, I
would mark "Not agreed"

However, all is not lost. If you use Name | Define and see that a cell
reference such as =Sheet1$A$1. Then remove the $s and the name will be
relative not absolute. I find this use of names very confusing but that
could be old age
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

When copying formulas from one table to another, these formulas should
refer
to data in the table they're being copied to. But instead, the formulas
look
over their shoulders to where they came from. See the example:

Table named Table1.
Name Width Length Area
Floor 12 15 =[Width]*[Length]

If you just copy the lower right formula to a new table named Table2, you
get
Name Width Length Area
Floor 20 19 =Table1[Width]*Table1[Length]

Now in Table2, it doesn't matter what you type in for length and width
because the formula is looking at Table1. Why can't the copy command know
that the destination location has those same column labels and use those
instead of looking over it's shoulder? Excel has always looked forward
when
you paste formulas. A1*A2 will stay the same when you go from one sheet
to
another. If you screw up and try to put your formula in a position that
can't have an A1*A2, it will change to REF!. That makes it obvious that
you
have an error.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions