Best Of
Remote Work - Thoughts
Hello community,
I am curious how many community members are currently working remote, in the office, or some sort of hybrid, and also know what type of industry they are working in.
I work in local government, which is a sector that *tends* to be less innovative and forward thinking than private business, but to be fair this is not always the case in every state and/or city. That being said, our department head has very recently allows us to begin working a hybrid schedule.
As a Business Systems Analyst, I may need to provide support to 11 different departments throughout the city (Police, Fire, Parks & Rec, etc.) in any given week. Working remotely allows me to save a ton of time (and fuel) commuting across town from department to department. We know that obviously not every issue can be resolved remotely, and when that's the case we will obviously facilitate in person meetings.
I would also say that if the project is particularly CREATIVE in nature (ex. new software implementation), I typically advocate for these meetings to be held in person versus remote. I've found this encourages more connection and collaboration from everyone in the room. On the other hand, if the other more routine day-to-day tasks can be done remotely, allow employees the flexibility to complete those assignments remotely if it works better for them.
Would love to hear others thoughts.
TK

Re: IF THEN formula
@JBolan Spacing in formulas can be a crapshoot, but I find there is generally not an issue having spaces around operators and after commas within a formula; it also aids in readability. Smartsheet likes it this way too, and will even add spacing automatically. For instance, I can type this as a formula:
and once I hit enter, I see the correct result... and also that Smartsheet has corrected the spacing:
I would say the chances of Smartsheet changing this to match Excel is effectively ZERO. Smartsheet's spacing is far easier to read and use.
If you have a suggestion for improving tool tips, by all means share it HERE, but I'm not sure how much better it can get than what shows up for each function after you type the function and the opening parentheses. I mean, you've got the syntax, an example, a summary, an explanation of the arguments, and a link to the function's help page all right there! The highlighted portion of the syntax even shows you what part of the formula you are currently in!
BTW, you can bookmark the links in my signature for the function help pages and the formula error message page. There also the awesome Formula Handbook!
Re: Consolidate unique values from multiple columns into single column
I turned off the publishing because there was another more efficient solution presented by someone else that uses the SUBSTITUTE function and adjusting the placement of the SUBSTITUTE to essentially place a locator. I must have forgotten to include a link to that thread when I did that. I can't remember who posted it though, but I have tweaked it a bit.
First you will need a text number column (called "Number" in this example). In this column you would manually enter the numbers 1 through however many you will need.
Then in the column you want to parse the list down, you would use something like this:
NOTE: This is under the assumption that you will not have ! (exclamation point) or | (pipe) in any of the data you are wanting to pull through. If you are, then you will need to use different punctuation in the formula. This is also assuming that the list you want to remove duplicates from is all in a single column. You may need to adjust the List:List range if you are pulling from multiple columns.
=IFERROR(MID("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", [email protected])) + 1, FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", [email protected] + 1)) - (FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", [email protected])) + 1)), "")
Formula is in [Column2] of the below snippet:

Getting list of archived projects from Resource Management
Here is how you can currently get a list of archived projects from Resource Management. This solution does not explore Bridge or integrations using API.
- Resource Management does not allow you to export the archive status or archive date of a project
- This should be fixed.
- This could be possible this is possible through Bridge or integration.
- You can see a list of archived projects using the Project Portfolio
- https://rm.smartsheet.com/projects
- Set “Project State” to “Archived”
- There is no good way to export this data. Copy and paste into excel puts each value on a row so additional work is required to make the paste useful.
- You can export a list of Projects with “Project State” = “Active”
- https://rm.smartsheet.com/addmultipleproject
- Click “Export project list”
- You can identify if a project is archived using that export with the following method.
- Overview
- Checks if project is on the most recent Project List export and, if not, marks it as archived.
- Create a Smartsheet grid sheet to receive the Project List
- Include all fields from the Project List export.
- Include “Modified” and “Modified by” fields.
- Add these two fields.
- “Last updated by Data Shuttle”
- Date field
- Records date record was modified by Data Shuttle
- “Archived”
- Indicates whether record is an Archived project
- “Last updated by Data Shuttle”
- Data Shuttle – Load Project List into Smartsheet
- Create a Data Shuttle that loads the Project List into Smartsheet grid above
- Data Shuttle must be owned by a user that only adjusts sheets through data meshes, or data shuttles.
- Create an automation on the Smartsheet grid above
- Automation updates the “Last updated by Data Shuttle” field if:
- Last modified user = User that owns the Data Shuttle from above
- Automation updates the “Last updated by Data Shuttle” field if:
- Put the following formula into the “Archived” field
- =IF([Last updated by Data Shuttle]@row <> MAX(COLLECT([Last updated by Data Shuttle]:[Last updated by Data Shuttle], [Last updated by Data Shuttle]:[Last updated by Data Shuttle], <>"")), "Archived", "No")
- Overview
Regards,
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology

Copying Rows and their attachments work around
Currently Smartsheet doesn't have the ability to copy rows including their attachments.
I've found a workaround that is much quicker than uploading all the attachments individually on other rows. This is especially useful if you have MANY rows that you need to copy including their attachments.
Save your sheet as a new sheet. On the new sheet, create an automation where whenever a row is changed, copy that row to your original sheet. Then you can just add a period or something to all the rows you need to copy. They will all move to your original sheet with the attachments. Drag those rows to where they need to go once on your original sheet.
This workaround saved me a ton of time. I hope this helps someone.

Re: Update on Outbound Cell Link Indicators
Hello all! I'm happy to inform you that sheets and reports will now display up to 100,000 cell link indicators again! See this post or the cell linking help article for more information.
Thank you for your patience,
Allegra Silcox
Sr. Product Marketing Manager
Generate Docs - How to fix Font Size Issues - a guide
I'm was using Generate Form to create a PDF I needed. While I was getting the content I needed, the font size in the form was wonky and inconsistent. The first 3 fields were in LARGE FONT size while the rest were a standardized. I don't know why or how the this happened. I was able to find a solution after an hour + of sleuthing. In hopes of saving others the hassle, I've attached a quick how to guide.

Re: Transpose stacked cell values into separate columns
Hey @BrianDN
Try these
Col7
=IF(FIND(CHAR(10), [Col6]@row) = 0, [Col6]@row, IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) >= 1, LEFT([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)))))
Col8
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 1, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)), LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)) + 1), IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) > 1, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1) + 1), FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2)) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 1)))))
Col9
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 2, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2) + 1), LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2)) + 1), IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) > 2, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2) + 1), FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 2)))))
Col10
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 3, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)), LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)) + 1), IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) > 3, MID([Col6]@row, FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3) + 1), FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 4)) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 3)))))
Col11
=IF(LEN([Col6]@row) - LEN(SUBSTITUTE(JOIN([Col6]@row), CHAR(10), "")) = 4, RIGHT([Col6]@row, LEN([Col6]@row) - FIND("~", SUBSTITUTE([Col6]@row, CHAR(10), "~", 4))))
Will these work for you?
Kelly
Poor Man's Datamesh
Prior to getting Datamesh, I devised a solution to transfer data from one page to another, without having matching columns. This process uses an intermediary sheet. It's kind of a pain to set up, but it works well and requires little to no maintenance. I still find some very small use cases where this comes in handy. Thought I would share.
Below is an example of a source and destination sheet, with an intermediate "cleaner" sheet. Requirements:
- The source sheet and the cleaner must have a reference column with shared unique IDs. I'll tell you how to manage that below.
- The cleaner sheet must have the same column headers as the target sheet.
Setting up matching unique IDs between source and cleaner sheets
You may have an active sheet with data constantly being added. You may not even have unique IDs associated with the data, or they may be unpredictable. What you need is a predictable unique Id in the source sheet. To get that, set up a Row ID column. This will provide a sequential row ID for every incoming row. In the above example the row IDs are 1-5.
Then, in the cleaner sheet, add a list of numbers to cover both your known rows (i.e. "1-5") and enough future rows so that you aren't having to constantly go back and add more. I usually load about 3,000 at a time, and I don't have to look at them for a few months. You can easily create a column of numbers in excel and then copy and paste them into your cleaner's reference column, 500 at a time.
Mirroring Data in your Cleaner Sheet
Now that you have a unique id to use as a reference, use cross-sheet column formulas to bring over the data you need, in the format you need it in. In the cleaner sheet example, I merge the first and last name into one column to match the target sheet. I also remove the color from the house information. The formula for "Client Name" would look something like this:
=INDEX(COLLECT({source_firstname}, {source_rowid}, [Extra Column]@row),1) + " " + INDEX(COLLECT({source_lastname}, {source_rowid}, [Extra Column]@row),1)
Send the Data Via Automation
Use an automation to transfer the data to the target sheet. A few things to keep in mind:
- You may need to set some conditional cross-sheet logic to trigger your information to appear at the correct time. For instance, you may only want the data to transfer once a checkbox on the source sheet has been checked. You can set your cleaner sheet up so that it only populates data if the corresponding checkbox is checked, or other condition is met.
- It's up to you whether you want to use a MOVE or COPY command. I've found that using a MOVE command is nice because it essentially deletes the row ID from the cleaner sheet and makes it impossible to accidentally send more. On occasion, I've added an ID back in to re-do a transfer. Using "COPY" command would keep the row ID on the cleaner page, but you may need to have further logic to keep from re-triggering the automation and sending the row again.
- You can't trigger an automation using a formula (they do this to keep from accidentally triggering an automation repeatedly). However, you can set a time-based automation to send based off a formula column. I had one instance where I set up 12 of the same time-based automations, each for a different hour. Classy, I know.
Handy Trick
Remember, your cleaner column headers must exactly match your destination column headers. And your move command will send every column, including the row ID column on your cleaner page. However, move automations can't overwrite column formulas. So if you name your row ID column in your cleaner sheet the same as a column in your target sheet that's got a column formula assigned, all the data will copy or move over except the row ID, as that will essentially fail to send.
Use Cases
There are plenty of use cases for this trick. I originally devised it because I had to have a continuous merge of data from three separate sheets that didn't look at all alike. I used a cleaner sheet to parse all that data. In that case, I actually had three sets of unique IDs, one to match each sheet. Complicated? Yes. But it solved an impossible problem, so there you go.
Re: Using IF with Networkdays
@Pamella Souza I have tested your request and here is your formula that works - attached example image for your help. Make sure your date columns are all column type "DATE" sometimes the smallest things get overlooked! :)
=IF(NETWORKDAYS([Start Date]@row, [End Date]@row) <= 45, "YES", "NO")
Cheers!