Helpful Things I've Discovered About Smartsheet (Long)

Jim Hook

I've been using Smartsheet for about 2.5 years now and gradually discovered how great it is for many tasks within a our small consulting company. While I originally started using Smartsheet for its project scheduling capabilities on large projects, I've ended up using it for much more. During that time I've discovered many features and behaviors of Smartsheet that I didn't see in the early tutorials I watched that ended up being important to me. The items below don't deal with project scheduling since that's not our prime use of Smartsheet today. Many of you are probably more experienced with the ins-and-outs of Smartsheet, but for newbies here are some items that may not be apparent in the tutorials and help pages:

 

1. Smartsheet has amazing linking capabilities, far beyond what I was used to in Excel. The ability to link literally hundreds of cells between two sheets in one operation enables connections that wouldn't be practical if you had to do them one at a time. There are some limitations, though. You can only link 500 cells at a time and the maximum number of incoming links on a sheet is 5000. That can be extended slightly since it lets you go over 5000 if you are already close to 5000 and you link 300 more, for example.

 

2. While speaking of Smartsheet limitations here are the main ones: 5000 incoming links; 5000 maximum rows; 200 maximum columns; 200,000 maximum cells; 1MB maximum save size. I've only ever run into the first one.

 

3. If you remove an incoming link from a cell, the old value stays there. Likewise, if you delete a sheet that is providing incoming links to other sheets, the old values stay there.

 

4. When you move a row via drag and drop the links all stay as they were. This doesn't work with copy/paste.

 

5. When you export a sheet to Excel, the formulas are not kept. However, you can call Support and have them set a switch that causes the formulas to be exported also. This works great for exporting normal sheets but it messes things up if you export a report to Excel since some of the cells referenced in formulas typically aren't part of the report.

 

6. The time it takes to save very large sheets is highly variable depending on what changes have been made. When you insert or remove a row, and immediately save it, it can take up to 45 seconds to save it (that's my worst case). This happens when the sheet is large and there are a lot of complex formulas in the sheet since it has to revise all the formulas that reference cells in rows below the change. Knowing this can help you organize your sheets to minimize the save time if it becomes an issue.

 

7. The SUMIFS function allows you to do what amounts to database queries on blocks of data linked in from other sheets and then perform calculations on them. For example, in our project timekeeping Smartsheet app I get around 90 records per week where each record includes a project name, the person's name, the function they performed, the hours worked and a brief comment. I gather them using a Smartsheet report that takes data from each employee's timesheet and then copy/paste that information from the report into a master project timesheet. The SUMIFS function lets me organize that virtually random data by project then function and hours each week as well as do billable/non-billable analysis by group and person. I looked at other database products on the market and most of them require the services of an experience software developer to do queries and calculations on data in a database.

 

8. While I was slow to begin using reports initially, I use them for a variety of items these days. As mentioned above I use them for extracting data from employee project timesheets weekly and to generate formatted tables of data that I then export to Excel for generating weekly charts used by managers and supervisors.

 

9. I make extensive use of nested IF functions to validate incoming data to make sure it is within expected ranges or that things like project names match our list of active projects. This avoids letting garbage into a sheet that would mess up subsequent calculations and makes finding bad input easier.

 

10. I find that cell history is great for tracking down when data came into a sheet or who entered it when trying to figure out where/when errors originated.

 

11. I make extensive use of Conditional Formatting to highlight items that are nearing or out of expected range. For example, a project financial analysis can highlight the net profit in yellow when a project is getting close to breakeven or in red if it starts to lose money. With a list of 40 active projects this makes spotting the ones in trouble much easier.

 

12. I use Highlight Changes frequently to identify what has happened since the last time I reviewed a sheet before doing final processing on the week's project timesheets, for example. I typically export the weekly project timesheets to Excel for archiving before I recycle them for the next week and want to make sure I have the latest information.

 

If I've gotten something wrong, I would appreciate your feedback.

Alina Anderson

Great list, this will be helpful to many users. Thank you for posting!

Brett Evans

Great tips Jim.  I need to try #7.

JohnHinkle

Awesome post! Thanks for sharing! 

Tim Meeks

Jim,

 

Thanks for sharing. Those are some really good things to know. I'm going to send out to my team of users at my office to help them.

 

Favor, could I get a template of the sheet mentioned in #7, obviously with dummy data but with the SUMIF formula in place?  (I'm not as good as Brett is with formulas  and need help in creating.)

 

Here is my email - tim.meeks AT mdhs.ms.gov 

 

thanks,

Tim 

Jim Hook

Hi Tim,

 

I'll put something together later today and either share it or send it to you.

 

JIm

Brandon Elmer

Hi Jim, 

 

My boss has asked me to put a time keeping system together for a team of 3 engineers.  I'd like to do what you're doing.  Can you also share with me?  brandon.elmer@aventurahq.com.  Thanks!

 

Brandon

Brad Jones

Here's my tip for those people who use Charts.

Normally, once you go to edit a chart it will turn off the sharing feature.  There is a way around this.

 

1. Edit your chart as you would normally.

2. Finish the chart and when it closes you will see that it is no longer shared.

3. Re-fresh your browser page and you will see the chart as shared again and with the same original link.

 

I used to edit multiple sheets and then go through and re-make all of the sharing and hyperlinks, but using this method, I no longer have to go re-make all of my sheet hyperlinks or edit links present in other SW platforms.  Finally an easy way to manage them when data changes.

Alejandro Lamothe1

Another limitation I Found is the maximum number of rows you can export in a report, according to my experience is only 20,000, or maybe I am doing something wrong.

There is also another limitation regarding this issue, and it is that the report only shows blocks of 500 rows at a time.

The way  I found around the limitation of the export is to sort the rows in the opposite way as they were on the first import (only the first 20,000 rows will be exported, most recent rows for instance) and export the report again, and yes it will export the first 20,000 rows, this way at least you double the  export capacity of reports in Smartsheet, I want to be wrong, but I do not think so

Tim Meeks

Alejandro,

you are probably reaching some of the standard size limitations within Smartsheet that are documented. For importing it is 

• The file cannot exceed 10 MB
• Up to 5,000 rows, 200 columns, or 200,000 cells can be imported at a time

It may be that your specific report allows 20,000 due to the parameters above.

I didn't see it documented on report export size, but this lines up with other size limitations I've seen.

thanks for the info.