copy down

[Greg L.]

I'm trying to merge three columns into one (first name, middle initial, last
name). I then want to copy the formula down but it's only incrementing the
last cell in the formula.

here's what it's doing:
=q2&" "&R2&" "&p2
=q2&" "&R2&" "&p3
=q2&" "&R2&" "&p4
=q2&" "&R2&" "&p5
=q2&" "&R2&" "&p6

How do I get it to increment each reference without doing it manually? I'd
like it to be like this:

=q2&" "&R2&" "&p2
=q3&" "&R3&" "&p3
etc.

thanks.

 

[Eduardo]

Hi,
I copy the formula as you posted and when copy down it works Ok incrementing
each section

 

[Greg L.]

any reason why it wouldn't be doing that in mine? It's only incrementing the
last item p2 to p3 to p4 but not q2 and r2. I'm using Excel 2007 and
calculation options is set to automatic.

 

[Fred Smith]

It works for me. Are you sure this is the actual formula? How are you
copying it?

Regards,
Fred

 

[Dave Peterson]

Make sure that the cells getting the formulas are formatted as General (anything
but Text).

It could be that the cell that gets your initial formula is the offender!

 

[Greg L.]

ok I tried it in a new worksheet and it also works. The worksheet I'm using
was created from a macro executed on a text file. Is there any special way I
need to save it so these formulas will work correctly? It's quite a large
file. I've copied and pasted values only into a new one but still get the
same problems.

 

[Atishoo]

Q. Is the second cell in your series blank or are you autofilling from a
series with two formula already in it?
If your first cell contains
=q2&" "&R2&" "&p2
and second cell contains
=q2&" "&R2&" "&p3
then autofill will only progress the p2.
if you only have one cell containing
=q2&" "&R2&" "&p2
then dragging the fill handle should give you
=q3&" "&R3&" "&p3
Or at least it did with mine!
You can also automatically fill a formula with refference to adjacent cells
downward by double-clicking the fill handle of the first cell that contains
the formula. If you have names in cells Q2:Q15 and R2:R15 and P215, and you
type the formula q2&" "&R2&" "&p2 into cell S2. To copy that formula into
cells S2:S15, select cell S1 and double-click the fill handle, it will only
copy as far as S15

double check that the cell above your first cell with the formula you wish
to autofill does not contain =q2&" "&R2&" "&p1

 

[Eduardo]

Hi,
be sure that your cells are format as general not as text

 

[David Biddulph]

Are you sure that you've got
=q2&" "&R2&" "&p2
and not
=q$2&" "&R$2&" "&p2 ?

 

[Greg L.]

Turns out it looks like it's the macro that reads the initial txt file and
creates the spreadsheet. If I simply open the txt file and manually format
the cells and then use the formula it works fine.

So, I will continue looking at the macro (I didn't create it) until i can
figure out why it's causing problems!

Thanks to everyone for your assistance.