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.
We want to implement single-sign-on at work and have received approval. However, our IT department wants all external partners to have/use Mayo credentials to log into Smartsheet. Not all of our external partners ever set foot on our campus and don't have our company credentials. I have a couple of questions. Has any other…
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…
Cant find this anywhere
