Sign in to join the conversation:
Simply trying to combine FirstName and LastName columns into a new sheet.
=JOIN({FirstName}1:{LastName}1, "-")
This come out #UNPARSEABLE
there are a couple ways to so this, the best way is somewhat complex.
If you refer to the cells individually you are going to run out of intersheet links quickly, you only get 100 of them. In order to use the most out of your available links, you need to use ranges and parse what you want out of them based on their location.
To do this you will need to make 2 column references, i'll call them {first} and {last}
Now we can take a look at how to "Join" them together. First we can use an index formula to return the value from a location.
Example:
Raw Data:
Row# Column1
1 A
2 B
3 C
4 D
5 E
Formula =Index([Column1]1:[Column1]5,1)
will return: A
Formula =Index([Column1]1:[Column1]5,3)
will return: C
We can use this to parse the data out of our ranges, then concatenate them. The formula should look something like this
=index({first},1) + index({last},1)
Now this will work perfectly, but it will set you up for a lot of manual work, because you need to change the "1" for every single row to reflect the next value. instead what I recommend is to add another column that simply counts up (I'll call it "Counter"). You can either do an autonumber, or what I recommend instead, just type 1 in the first row, 2 in the second, highlight both and drag it down to count up.
Once that is created, just change both of the "1"s in the index formula to reference the "Counter" Column.
=index({first},Counter1)+index({last},Counter1)
You could also build the counter into the formula itself without the additional helper column. You would put
=INDEX({first}, 1) + INDEX({last}, 1)
in row 1. Then in row 2 you could put
=INDEX({first}, COUNT(Name$1:Name1) + 1) + INDEX({last}, COUNT(Name$1:Name1) + 1)
The COUNT(Name$1:Name1) + 1 will count how many rows you have starting from the first row (Name$1) and then stopping at the row above the formula (dragfill will auto-update the row number in the Name1 portion, hence the lack of a $ to lock that in like in the first one). We then add 1 to give you the current row count, and that replaces the additional helper column.
Not to say the above is wrong, but it is another option.
Agreed, the fewer columns the better.
Most of the time. Some of the crazier mess is easiest if you spread it out. Haha.
I have a form that will be filled out internally. There's a cell labeled "Department" which contains 8 dropdown list values. There's a another cell called "Executives" which are the leaders of these departments. In the form, I'm attempting to do a logic where when you choose a specific "Department", then the cell…
I shared access to a user to a specific folder under workspace A (folder is 2025-09-Service Factory). However, I can see that he's shown to have access to all the other child folders under 02-ACTIVE Initiatives. Why is that when I only shared out one folder? Is there a way to only share access to users to ONLY specific…
what i can do if all my existing smartsheet gant files can't be open after change my plan to Pro? what could be the reason ?
