0

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!

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

1 Answer 1

0

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:

Last_name
First_name
Class

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?

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .