Excel - Concatenate Formula not working

Asked By hele on 09-Mar-09 10:37 AM
I have been using the concatenate function for a while now but recently it
just stopped working. these are numerical columns. i go to insert- Function-
concatenate, put in the cell #'s i want in the correct box and press enter.
all works according to plan.
however once i try to have the data extend down the column, it just shows me
the data from the first box on every box below it! What usually works is just
doing copy of the original cell and highlighting down. all the rest of the
data just appears.
it's not working and i have a deadline.
help me please!




DILipande replied on 09-Mar-09 11:00 AM
Hi Helen,

alternatively you can use following:-

=(Ref1)&""&(Ref2)&""&(Ref3)   ... and so on..!!
where Ref are the references in the excel worksheet which you want to
concatenate.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
dilipandey@yahoo.com
dilipandey@gmail.com
New Delhi, India
Click above to get my email id replied on 09-Mar-09 11:13 AM
If you use
=CONCATENATE(A1,B1)
in C1 then it will change to
=CONCATENATE(A2,B2) if you copy it down to C2...

Pl. make sure that you do not have absolute references in the formula
(addresses with $ in it like $A$1...
Dave Peterson replied on 09-Mar-09 11:19 AM
Make sure you are in automatic calculation mode.

In xl2003 menus:
tools|calculation tab




--

Dave Peterson
hele replied on 09-Mar-09 11:30 AM
I tried both the way you and Dil suggested, "=CONCATENATE(A1,B1)" adn
neither worked.
my columns are completely numeric. i dont think my stuff is in absolute
reference (there are no $ signs in the formula bar).
any other ideas?
thanks
Gord Dibben replied on 09-Mar-09 12:45 PM
Turn on automatic calculation under Tools>Options>Calculation

Or Office Button>Excel Options>Advanced>Formulas


Gord Dibben  MS Excel MVP
hele replied on 11-Mar-09 09:12 AM
hi everyone,
yeah, my automatic calculation thing was on but for some reason, i tried to
try the formula again and it suddenly worked.
no idea why or how but still very happy about it.
thanks for all the advice,
hendy