Excel - Concatenate Formla help?

Question

Hello

In cell D1, I have the following formula

=CONCATENATE(G1, A1, " ", B1, " ", C1, G2)

In cell D2, I have the following formula

=CONCATENATE(G1, A2, " ", B2, " ", C2, G2)

I am trying to fill the next 1000 cells in column D with the above forumula.

Essentially, G1 and G2 should never change in the formula, yet the reference to A, B, and C should match what row number it is.

When I highlight, the first two entries listed above and then drag the forumla down the column, the references to G also change to match the row.

How do I keep them fixed to just G1 and G2?

Thank you

Answer 1

When you want to reference an absolute cell in Excel (which is what you are saying about G1 and G2) then use the $ as part of the cell reference.

So put in $G$1 and $G$2 or for any cell that should not change. Now the fill handle will change the other cells appropriately but not G1 and G2.

As a note, cell references by default are relative, so they will change as you drag the fill handle which is great most of the time.

Answer 2

change G1 and g2 to
$G$1 and $G$2

once you have your formula, you can double click on the fill handle and the formula should follow all the way down as far as the rows on the left go.
Another way is to select the entire range you want the formula then write the formula, (don't forget the $G$ stuff) thats called absolute by the way, you can also press F4 to get the dollar signs....
so once you have written your formula, hit Ctrl Enter
your formula will travel throughout the selected range

Answer 3

Sure, just paste this

=CONCATENATE($G$1, A1, " ", B1, " ", C1, $G$2)

Then copy and paste it into the next 1000 cells below

Enjoy my profile, I am the VBAXLMan