Applying names.

  • Thread starter Thread starter mika.
  • Start date Start date
M

mika.

Hello,

In my worksheet, I have a name defined for Column AA as
P_Total_Before_Tax and a name defined for Column AB as
H_Total_Before_Tax. In Column AC Row 2, I have the
formula "=SUM(AA2,AB2)." I want to change this formula so
that it uses names instead of references, so I clicked on
that cell and went Insert --> Names --> Apply, and
selected P_Total_Before_Tax and H_Total_Before_Tax from
the list. But I got a message saying "Microsoft Excel
cannot find any references to replace." I then tried to
change the formula manually to "=SUM
(P_Total_Before_Tax,H_Total_Before_Tax)" but then that
seems to sum both columns or something, rather than just
whatever is in Row 2 in those columns. Can anyone help me
figure out what's wrong and how to fix it - that is, to
get the formula to use cell names and only calculate
what's applicable to the current column?

Thanks kindly,
mika
 
Hi Mika,

If the name applies to the whole column, just replacing your reference with
the column name is too much, because it will then sum the whole column, as
you already noticed.
What you need is the intersection of the row where the formula is with the
named column.
One way of doing this is to multiply by 1 or add zero; this is called
implicit intersection.
So if you change your formula to =SUM(AA2*1,AB2*1), applying the name will
work correctly.
However, it would be mauch easier to change your formula to =AA2+AB2. Then
you have no intersection problems, and Applying a name will work fine.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
That worked beautifully. Thank you!
Mika
-----Original Message-----
Hi Mika,

If the name applies to the whole column, just replacing your reference with
the column name is too much, because it will then sum the whole column, as
you already noticed.
What you need is the intersection of the row where the formula is with the
named column.
One way of doing this is to multiply by 1 or add zero; this is called
implicit intersection.
So if you change your formula to =SUM(AA2*1,AB2*1), applying the name will
work correctly.
However, it would be mauch easier to change your formula to =AA2+AB2. Then
you have no intersection problems, and Applying a name will work fine.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel




.
Click to expand...
 
Back
Top