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 inly one user in my trial account. When I am trying to upgrade to PRO plan it is defaulting for 2 users and not allowing me to upgrade the plan for only one user. Please help.
We are curious if anyone is experiencing issues with automations that are to generate emails. We've been seeing gaps with a number of these automation on various sheets, where the sheet will record a date of when the email was sent, but it is either never received by the recipients or it is received hours or a day later.…
I can create a Chart in Dashboard from sheet well but from Report is not working now. I just try with simple Report (just contain 2 column Primary and Number) but cannot make the chart. Is there any changes recently right, because I haven't worked with Smartsheet dashboard for a long time and I remembered that I could do…
