Sorry in advance if I don't explain this very well, definite newbie here when it comes to spreadsheets.

I have a Text only spreadsheet with 4 columns, first 3 are Student's Last name, First name, and Class. 4th column will be concatenate of the other columns. I'm using this formula

=CONCATENATE(A2;" ";B2;" ";C2;".jpg")

I'm using this to create file names for student photos, hence the .jpg and yes I do want the output to have spaces in between, eg. John Smith Senior.jpg The problem I have is duplicating the formula throughout the column and have the Row # change. I can duplicate it now, by click-dragging the bottom right corner of the cell, but Row #'s don't change. Need help with this! Don't wish to go through hundreds of rows and manually change the formula!

Thanks for any help!

  • Is that exact formula you are dragging down? Are you certain there are no "$" in the formula? – andy holaday Mar 1 '14 at 1:21
  • Yes exactly as that and with $ eg. =CONCATENATE($A2;" ";$B2;" ";$C2;".jpg") both do the same thing... – Tom Mar 1 '14 at 1:36
  • So, not exactly that, then? Remove the $ and drag/copy down. – Alex M Apr 5 at 16:01

I can't fathom why your formula does not work. Since it doesn't, and this work-around idea is too long for a comment, I'll offer an answer.

I have your data organized like this:

|   |     A     |     B      |    C     |
| 1 | Last name | First name | Class    |
| 2 | John      | Smith      | Senior   |
| 3 | Jim       | Dean       | Junior   |
| 4 | Abby      | Normal     | Freshman |

The first step is to select the entire area, headers and all.

Next, press Ctrl+Shift+F3 to invoke the "Formula | Defined Names | Create from selection" tool. In the dialog, make sure only Top row is checked. OK.

This will create three named ranges:


Next in D2 put this formula:

=CONCATENATE(Last_name," ",First_name," ",Class,".jpg")

or for your regional settings the formula might be

=CONCATENATE(Last_name;" ";First_name;" ";Class;".jpg")

Fill down.

Now what do you get?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.