Summary Cells & Adding Rows

tom.wnuk

If I understand Smartsheets correctly, all formulas must reside within the sheet where the data exists meaning, if I want to display a metric or a total on a Sights Dashboard or Report respectively, I cannot.

So, what do you recommend I do to create summary cells on a sheet where rows will be continuously added?

Andrée Starå

Hi Tom,

You could add a summary cell that's displayed on the sight if it's one sheet or use cell linking to add summaries from different sheets and then add that summary to the sight.

I hope this helps you!

Best,

Andrée

tom.wnuk

Thanks Andrèe, that's what I have as additional columns but it's really a hack and was just wondering what others are doing.  

The one-off solution is simply b/c SmartSheets doesn't support cross-sheet linking/lookups which I heard is coming. 

thanks again.

-tom

Andrée Starå

Happy to help!

J. Craig Williams

Setup your sheet to have 'sheet level' KPI's. 

Totals, counts, averages, whatever is important.

Cell link these KPI's to a 'group level' sheet. 

More consolidation, counts, averages, whatever is important,

Cell link these KPI's to a 'master level' sheet.

At each 'level', the cells that are being used to aggregate can be displayed on Sights as well.

Changes at the lowest level (where the work is done) will be displayed at higher and higher levels for those that need to see it.

It isn't as hard as it may sound, it just takes planning.

Craig

 

wpugsley56596

What is a "KPI". Smartsheet seriously needs to do an IN DEPTH  demonstration of how this would work.  I'm tying to get my company to adopt SM and IT'S THE FIRST THING MY BOSS ASKED!  

Whenever you create new rows all the formulas go to hell and don't work.  I'm not saying that YOU can't make it work but really why is Smartsheet avoiding this topic.

Only program I'v found that can do this easily is FileMaker Pro but it doesn't have the GANTT stuff and lags behind Smartsheet in many other ways as well.  Like drag and drop and GANTT drag and drop.

All I'm tying to do is create a sheet for bidding custom signs, then turn that bid into and order and track it through the shop. I'm always going to be adding items the customer wants to change and can't be rewriting formulas and dragging cells down or over to create summary fields. I can't rely on the many different users to do this correctly.  It needs to be automatic.  You add another row and the summary field moves down and does the calculation.

J. Craig Williams

KPI: Key Performance Indicator. 

ie what is important (to you, your team, or your boss)

https://en.wikipedia.org/wiki/Performance_indicator

Apologies for the TLA (Three Letter Acronym)

Once set up (properly), the consolidation will be (should be) seamless to your users.

There are a few of the formulas that are oft-used (IME - in my experience)

SUMIFS and COUNTIFS are top of the list.

You can read about specific functions here:

https://help.smartsheet.com/functions

and this points to some useful articles on the formula concept

https://help.smartsheet.com/topics/formulas-and-functions

If you are really struggling and have a small budget, contact me or Andrée for private mentoring help. (There's other consultants, but ... you know, we're here (we don't work together))

Craig