Prevent deletion of rows if it contains locked cells?

J. Craig Williams

Tamara,

 

You won't go back.

For a wide sheet (50+ columns), I find many mini-Reports are better than trying to edit the sheet directly. I can create Reports to show only the half-dozen columns of interest. 

Add in the ability to hyperlink to the Reports and the system smooths out a lot.

 

Craig

Tamara K

Thanks Craig. Yes, this is going to be a much better solution.

Re hyperlinks: are you saying you place hyperlinks from one report to another right there within a report?

J. Craig Williams

Tamara,

 

It needs to be in the sheet, but yes, I add links to Reports directly in a sheet that show up in OTHER Reports.

 

This allows the user to see a Report that shows an overview of the project and a Report that shows detail of the project.

At an upper level meeting if all the Status = Green, there is no reason to show the details, but if they are Yellow or Red, the details are only a click away.

 

Craig

Tamara K

Sorry...one more question:

Shaine mentioned the following drawback to using reports: "...sheets must still be opened by someone and saved for the latest data to appear in the report."

 

If users are ONLY using associated reports and forms to interact with the data, what happens?

 

Here's why I ask:

I was able to create a form where end-users input data onto the sheet. Then, I created a report where managers can see certain entries and edit those entries and save a change. I was able to refresh my screen on the report and the report updated, based on the filtering I had set up (the row I edited disappeared because it was no longer fit the criteria for appearing on the report). When I went in later to view the originating sheet, the edit from the report did reflect back to the main sheet. What would happen if no one ever went into the main sheet? Everything seems to be working normally, but I feel like I'm missing something, based on Shaine's comment above.

 

J. Craig Williams

Tamara,

 

I am not quite sure what Shaine was referring to.

Edits made to the underlying sheet are saved in the sheet, regardless of whether someone has opened the sheet or not.

 

What he may be referring to is sheets that use the TODAY() function won't necessarily update the calculation if the sheet is not saved directly.

 

For small numbers of sheets, this is the solution I use:

 

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

UPDATE: 2017-0917 - my solution has changed slightly and the post updated to reflect new method.

Craig

 

Shaine Greenwood

Tamara,

 

Craig hit the nail on the head—sorry if I wasn't clear. Right now, conditional formattting and functions with the TODAY() formula won't update in the report unless the source sheet is opened and saved.

 

You only run into this if you have any conditional formatting rules or formulas in your sheet grid that make calculations when compared with the current date. For instance, if you have a conditional formatting rule that applies formatting if a due date column is in the past, or in the future, or x number of days before or after the current date, then someone will need to open the source sheet and make a save, or update a cell in the report that references that particular sheet. Otherwise the formatting won't change.

 

If you don't have conditional formatting rules that work with the current date or formulas that use TODAY() then you don't even need to factor it into your daily workflow.

Tamara K

That's great news all around! Thanks for the clarification Craig and Shaine.

J. Craig Williams

Shaine, 

 

I never got bit by the conditional formatting issue. Good to know!

 

Tamara,

 

Glad you got what you needed.

 

Craig

Lulu peru

All of your questions are huge and I just have a simple..... how do I delete information with a lock on it? I was given his page, was told they would delete the information that was in there, and if he really did delete the information, they came right back.  I have tried to delete them but I can't.  



Thank You... 

Lulu peru

Shaine Greenwood

Hi Lulu,

Only people Owner and Admin sharing permissions can delete information from a locked row. You can see what sharing permissions you have by clicking the Sharing tab at the bottom of the sheet.

If it says Editor, you can't delete the information out of the locked row. You'll need to get the Owner to give you Admin sharing permissions with the steps in our Help Center: https://help.smartsheet.com/articles/520104-sharing-sheets#remove

AriEsor

Hi All, 

Thanks Brad for keeping this thread going. Please add my name and that of my entire company to your votes for an update to the user levels. My question is we use smartsheets to do our budgets for approvals which we are able to export to excel for a comprehensive budget for 17 different sites. We have an issue where the information is being deleted, we cannot find out who nor do we really care. At this point we're assuming positive intent...

But despite that, we still want to limit the users' ability from being able to delete rows, as deleting a row, deletes all the history, including approvals and copies of receipts for monies being spent by each site. I want to know:

Will the mini-report function address this issue for example- 

1- Will our users still be able to add rows for approvals/rejections and receive approvals/rejections as well as attach docs to each row using only the report function?

2- Will we be able to shut them out of the master sheet while only giving access to the reports?

3- Will this affect any of the exporting we have to do to in excel?

4- Can we run reports on different values in a column/row or only on individual rows?

Thanks I may have more questions :-)

Galen Garrison, PMP, CSM, MBA

AriEsor,

I had a Smartsheet where users would accidentally delete data and sometimes rows. What helped me to learn who, when, and what was enabling Alerts --> Notifications --> When anything on the sheet changes, email me right away. 

I created an Outlook rule that files these emails in a particular folder so that I can review the information if needed.

Good luck.

Cheers,

gg

Brad Jones

Ari,

Unfortunately, the only 'workaround' I have heard for this is to lock your sheets down tight and only allow users to change the sheets via webform or UpdateRequest - both of which are ludicrously impratical for any application we have yet employed at our company.

As for your questions:

1 - NO/YES - Within a report, you cannot add rows (but you can still delete them and attach docs)

2 - NO - If you want them to be able to edit the report as if they were using a sheet customized just for their access, then they have to be able to edit the sheet where the data is coming from. (exception being UR and Webform)

3 - Your question does not give enough detail to answer.  (but in general reports don't mess up any formatting, etc of your master sheet)

4 - Reports are built on using columns as the main criteria, not rows.

As you're new, you should take a run through some of the Report help files and videos.

 

Brad Jones

I agree, regular notifications and Outlook filters are a must.

One snag - the notifications don't tell you what row/comment/attachment was deleted, or at least they don't as of two weeks ago.  Tested it tonight: deleted a row, got the alert.  Now I know that one row out of 500 was deleted - but the notification does not tell me which row was deleted - only that a row somewhere on the sheet was deleted.

It's still a big guessing game as to WHAT was actually deleted.  Did not really test this with the Activity Log feature, and now my trial is expired, so I'm not sure if that will give you the specifics on deleted items.

The core problem remains that we should be able to keep users from deleting things.  Like an "Editor - cannot share or delete"

AriEsor

Thanks Galen and Brad! Your responses are helpful. We're considering moving up to business so we have access to see what was deleted and by whom. You're spot on Brad, I've been going through videos to get more acquainted as this is my Director's project, she's handing it off to me to correct the issues.

Galen, I think we're set up with the notifications but again my director will have to transfer the "parent" access to me so that I can manage. We have 17 different sites and at any given week we have at least ten items per site that needs approvals, so that creates a lot of notifications. If only they would update the user profiles to someone that can add info, correct their own added info and take away the function to delete. I was actually hoping there would be a way to lock specific rows so that only the site owners could make changes to them...but alas

That would be too much like right...

Thanks again all!