Combining Data from Multiple Sheets in a Sight

My team and I are having a problem with the combination of data from multiple sheets showing up in our sight. Originally, we had a couple sheets that all had formulas returning data at the top of each sheet. I then cell linked the cells from these sheets to allow one sheet to add them all together. This then allowed just that sheet to be exported to the sight so we only had one number for that set of data. However, now we have 7-10 sheets and the cell linking has made some of the most data-rich sheets incredibly slow and frequently crash-prone. I went ahead and removed all of the cell links and that helped solve the slow response and frequent crashes, but now I can't figure out how to combine all of the separate sheets' data into one number in our sights. Does anyone have any suggestions on how to do this without linking all of the sheets together?

Thanks for the help!

John Isenhower

Find more posts tagged with

Sheets

Formulas

Dashboards

Project Management

Cell linking

Comments

Richard Rymill SBP

John

I would look at Using a REPORT to summarise the data from the source sheets and while Reports will not add up a column of data into a total, with some creative design you should be able to get the Report to show that Data from a Row in one of your contributing sheets with a suitable data trigger.

Hope that helps?

RichardR

John Isenhower

Richard,

That makes sense and I went ahead and created a report to pull all of the data from the different sheets together, but my main problem is adding the numbers together to appear in a sight. In the current version of Smartsheet, is there a way to add all of these numbers together to show up in a sight, or will I need to highlight the numbers and manually plug in the total shown with the built in summation formulas in the bottom right of reports?

Thanks,

John

Richard Rymill SBP

Hi John

A design that my colleague James Rezin developed for the NHS to monitor their Projects was to create a dashboard at the top of each project sheet that sums the KPI activity in that sheet, then cell links that data to a portfolio sheet that lists all the projects in rows. In that Portfolio sheet you can carry out the Sums you needs to then show on your Sights dashboard.

You can also use the stats in the Portfolio sheet to create Smartsheet Labs Graphs and share those to interested parties via a unique link AND add that link to your Sights dashboard. It wont show you the graphs in the window (yet anyway) but one click from the link opens the graph to view.

A really good TIP when linking uniform data from Sheet dashboards to the Portfolio sheet, is to Create extra (hidden) columns to the right that simply list your KPI's in one row, read left to right so Grabbing a row of cells to link to your Portfolio is easy and less error prone than one at a time!

This way your Proper dashboard top left looks easy to read in a Box shape and the Linked cells are easy to grab for linking, so adding new projects to your Portfolio sheet is just a couple of clicks work.

Hope that helps?

RichardR

Michelle Watkins

Hi John,

You may want to reach out to Smartsheet sales and get pricing and a demo of the premium app, Pivot App. This app has helped me solve for similar needs you are describing.

A future integration access would be through this link: https://pivot.smartsheetapps.com/ with your main Smartsheet account.

OAP

Smartsheet is VERY SLOW - Especially if you use Formulas or 'too many' link - we had a sheet built for us using several Paid For design desk sessions by Smartsheet and when we tried to use it the sheet ground to a halt

We were told that it was a Browser issue - too many formulas for the browser

Rebuilt it in Google Sheets and no issues - works very smoothly so the issue appears to be with Smartsheet

BTW they have refused to refund the design desk sessions used to have the sheet built

Hundreds of $$$ down the drain

It also appears to be slow periodically on different computers with different browsers

- we never experience those issues with other software especially sheet systems like Excel and Google Sheets

- seems like there is some kind of known issue with smartsheet that they dont want to talk about??

Regardsing Pivit it is about 4 thousand dollars it looks good BUT it is a bit roidoculous to pay 4k to use it as a band aid for what a spreadsheet should be able to do anyway with just formulas etc.

It is ok to have a bandaid - just dont charge 4k for the band aid

Smartsheet needs to look into the primary issue of VERY SLOW sheets to the point of being unusable when even a moderate nukber of formulas are in play.