Need help with concatenate formula

[Guest]

=CONCATENATE(A2," ", B2," ", C2," ", G2,CHAR(10),I2," ", J2,
CHAR(10),L2,CHAR(10), M2," ", O2, CHAR(10), P2, CHAR(10), Q2, " ",R2)

When I copy this information to a new worksheet, the information just comes
out as #REF! and the above formula appears with all #REF! in it. I have the
column width wide enough.

 

[Lotus123]

It appears this formula is located in row 2 or higher on the original
spreadsheet. If you copy and paste this formula into row 1 of another
spreadsheet (or a row higher than the number of rows between 2 and the
row the formula is in), it will fail.

This is because the formula attempts to auto-adjust the row numbers (2
in this case) to something before row #1...as such, you will get a
formula that looks like this:

=CONCATENATE(#REF!," ",#REF!, " ",#REF!, " ",#REF!, ,#REF!," ",#REF!,
CHAR( 10),#REF!,CHAR(10),#REF!, " ",#REF!, CHAR( 10),#REF!, CHAR(
10),#REF!, " ",#REF!)

If I copy and paste your formula into row 2 or higher of my personal
spreadsheet, it works fine.

Note...use of CHAR(10) only works with concatenate if you have "Word
Wrap" enabled on the cell....just in case someone else is reading this
wondering why the CHAR(10) doesn't work on their machine .

 

[Biff]

That formula works fine for me.

When I copy this information to a new worksheet, the information just
comes
out as #REF!

When I copied row 2 to another sheet it copied just fine. Copying the data
should have no impact on the formula since it still refers to the original
data. When I cut row 2 and pasted into a new sheet the formula references
changed accordingly.

????????

Biff
..

 

[Guest]

The error suggests that when you paste it into the new worksheet you are
changing the relative references so that at least one of them refers to a
cell outside the sheet's boundaries. Here you are referencing row 2. If the
original formula was in row 10 and you are pasting it into row 9 on the new
sheet, all the row references would now be changed to row 1. *BUT*, if you
copy it into anything above row 9 the row references get changed to 0 or
negative #s, which Excel will reject with the #Ref

 

[Biff]

When I copy this *information* to a new worksheet

Apparently they meant:

When I copy this formula to a new worksheet

I guess I gotta quit interpreting posts so literally!

Biff