Cell Linking/Formulas in Reports

Stephen Annoni

Has anyone worked on implementing cell linking from reports to worksheets and customizing reports?

 

I dont see much in having reports if you cannot customize certain things after running, such as: formulas for totals in reports, conditional highlighting in reports, adding custom columns in reports. Also, without a cell linking function from reports you have to go through other steps like attaching the report to a new worksheet, running the report for the most accurate information, copy and pasting to the sheet daily. It is too much of an inconvenience.

 

In my opinion, reports should work similar to sheets where you can customize things like this.

 

Would love to see this implemented as it would take smartsheet to the next level.

Mike Wilday

This has been one of my pet peeves from the beginning. Reports are like fancy aggregated data from a sheet or multiple sheets. It's two-way only in that editing something that is gathered in a report goes back to the main sheet. However, you can't edit any field that has a formula. Which is a pain. You have to go back to the main sheet to edit the field. It makes sense in some measures to lock this data, but as an admin user, or if someone is given a particular role in a report, they should be able to overwrite formulas with hard data - or updated formulas if the need be. 

 

J. Craig Williams

Mike,

 

I very much disagree.

As a developer helping clients, I spend a lot of time early in the project correcting mistakes like users updating formulas. 

The formulas do not "remember" what they were before someone overwrites them - even the celll history shows only the displayed value, not the formula.

You also have limited ability to update preojct settings like [End Date], because there are things in sheet that have meaning and seeing only one row is seeing the data, in some cases, 'out of context'. The cell with the formula you are viewing in the Report may also reference something on the original sheet that has meaning, a column that is not shown, a row that is not part of the results, etc...

 

I prefer the pain of editing the sheet to the pain of trying to figure out why some other cell that relys on another cell is incorrect because someone edited something that they should not have edited.

 

This is NOT the same as being able to create additional formulas at the 'Report Level' to sum and count what is displayed in the Report. That is a different problem and one I would like to see a good specification. A main issue is that what I view the Report may be different than what you view, and calculating the sum based on that may be misleading or dangerous.

 

Mhy $0.02

 

Craig

Mike Wilday

i could understand that pain. I wouldn't want my report users updating formulas either, however, in some cases I have wanted people to hard code a date rather than use the formulated date to adjust a timeline - but there is no way for someone in a report to do that. I hated even more, sending them to the "official" data collecting sheet and making changes there. 

J. Craig Williams

 Mike,

 

Having the overright a formula means it is gone.

In cases like this, I have gone to a two column approach. 

 

Formula entry or manual entry.

The formula entry shows the formula result UNLESS the manual entry has a date in it.

The manual entry is labeled something like "Override Formula Date".

 

Craig

Mike Wilday

I understand your point and reasoning. But we are working with a series of formulas and I think if you allow only certain roles to override a formula I think it would be okay. Like if you assigned someone with an admin role. We are working with massive data collection sheet would have dates that were formulated across rows and needed to have users be able to enter a solid date to recalculate date formulas based on that date in the cell, but it was impossible because the date was initially set by a formula.

 

I think in rare cases, with the right privileges, it might make sense. Adding another column to that sheet would have driven everyone mad.  

J. Craig Williams

Mike,

 

I understand people's hesitation with adding columns. 

I have posted elsewhere the user levels and permissions are already convoluted enough without adding a "super admin" or "special exceptions". 

In Excel, the developer has the ability to lock down each cell, in every sheet in the workbook. Nearly every sheet i was ever provided access to either did not use this functionality or had abused it to the point where I was brought it to fix it.

 

If I had the ability to edit in a Report, as an Admin, it probably would be the same way I have the ability in a Sheet - no warning that what I am about to do is not a good idea.

For example, an Admin can edit a Locked row or column without warning.

It would be useful if there was a "respect the lock" setting - but would that be for everyone or just me?  would it reset to 'yes' when I close the sheet? Even though this would be useful, I have not (and am not) requesting it. 

 

A use case can be made for most things people can think of. 

As I sit in my office, it would be nice to have a door to the outside instead of having to go down the hall and out the front door. But I certainly don't want a door to the outside in every room in the house. Unless there is a fire. Or on Tuesdays.

 

Craig

 

Stephen Annoni

I think it might help if I better explain the situation.

 

I have a report ran that shows indivuduals on a certain team, and it shows how many tasks are late for each individual. What I would like to do next is add a column, or multiple columns. In these new columns, I would like to total each persons late tasks but this functionality is not in tact. This requires me to transfer data to another sheet. If cell linking was an option, I would not have to copy and paste data. If adding columns and formulas to a report was an option, this wouldnt be a problem. 

 

Sometimes, I don't want to label these columns because I want to create a sort of table on a sheet showing totals etc.. Instead I label vertically in the cells to keep data close and create a table as described. It looks quite nice.

 

I would like to include that these privelages should only be for an admin, considering I am one. I agree with that or atleast for people with edit rights. Maybe an option for admin to lock certain cells in this case would help. Sounds LIke a lot to add but essentially, it is just changing reports to have the same capabilities as worksheets.

 

J. Craig Williams

Stephen,

 

Saying "Sounds LIke a lot to add but essentially, it is just changing reports to have the same capabilities as worksheets" is like saying I want my car to have the same capabilities as a plane. 

Sheets and Reports look the same, but they are not, fundamentally, under the covers.

 

I'm not advocating against some way to consolidate and then analyze / manipulate the data within Smartsheet. However, I can do what you want in 3rd party tools that are designed to do so. PowerBI and AppSheet for example.

 

Craig

 

 

Keith Tham

I too am having similar issues with reports as Stephen and Mike are, for various reasons using a cell reference to make easier for default values (another SS workaround) that may or may not need overwirting.  Strangely enough for example the cell reference is overwriteable from a desktop but not on mobile?

If an admin action is allowed at the sheet level then should not the same permission level be allowed at the report level?  Having some of the standard SS tools to improve information on a consolidated report would be beneficial.  

Perhaps there can be a "Sheet" Builder/Consolidator similar to Report Builder for Admins vs Rolling up WS through linking.  If there were even a few rows on top of the report so can do some summarization would improve of report information.

Charts is a good example, which luckily found there is a lab for :

https://smartsheetlabs.com/

Even so had to manipulate the entire report to get a simple chart and is not possible to add to a dashboard, all details which unfortunately SS falls short.  

Row/Column) locking and permissions should be the tool to prevent formulas from being overwritten or better Cell locking.  Publishing report access could be another way to protect formulas but anything prior should be controllable by an admin/owner, again since have same control at sheet level and avoids unnecessary clicks opening and closing for simple corrections and changes.  

At this point am using SS to really only perform workflow steps (working well but with a lot of development effort) but now am looking at exporting the reports to Excel then automating import into a DB if necessary as found the SS limits, convoluted permissions and restrictions becoming a frustration challenge.

Craig appreciate your many posts as have helped immensely identify SS weaknesses but as well noticing just how many community enhancement requests are, hours with support, and time spend on workarounds which in my opinion indicates SS still has a way to go. 

Hopefully will see SS get these enhancements working in some release soon so not to have to spend so much time on workarounds.

Regards,

-Keith