Re: [libreoffice-users] Calc - allow users to format cells on protected sheets
On 2013-07-18 4:52 PM, Dave Liesse dslie...@liessefamily.net wrote: I think what the OP is referring to is that in Excel you can do whatever you want to a protected spreadsheet -- you just can't save the changes. That is actually a completely different issue, but one that I have been meaning to raise as a Feature Request - thanks for reminding me... :) Thus, you can do a lot of what if analysis but can't mess up someone else's work. As my last replies hopefully made more clear, 'm talking about the options available when 'Protecting' a worksheet... I need an option to 'Allow users to modify formats'... But alas it looks like this isn't going to be possible... sigh -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calc - allow users to format cells on protected sheets
Hi :) Ahh, i think it happens the other way too. That if you protect a document in LibreOffice then people opening it in MS Office will find the protection gets ignored. Personally i find it difficult enough to get people to edit and add their ideas into documents that are open and meant to be shared and updated. I've never had any trouble dissuading people from doing something! I had a quick look through documentation https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Calc_Guide but couldn't find anything. Are you sure you don't want to just share the document and then track changes? Regards from Tom :) From: Tanstaafl tansta...@libertytrek.org To: users@global.libreoffice.org Sent: Friday, 19 July 2013, 11:48 Subject: Re: [libreoffice-users] Calc - allow users to format cells on protected sheets On 2013-07-18 4:52 PM, Dave Liesse dslie...@liessefamily.net wrote: I think what the OP is referring to is that in Excel you can do whatever you want to a protected spreadsheet -- you just can't save the changes. That is actually a completely different issue, but one that I have been meaning to raise as a Feature Request - thanks for reminding me... :) Thus, you can do a lot of what if analysis but can't mess up someone else's work. As my last replies hopefully made more clear, 'm talking about the options available when 'Protecting' a worksheet... I need an option to 'Allow users to modify formats'... But alas it looks like this isn't going to be possible... sigh -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calc - allow users to format cells on protected sheets
Hi :) It sounds like a proper database would be better. Then reports can show the information in different ways, in different orders, without affecting the under-laying data. The front-end could be Writer and could be restricted to just certain fields (columns). Once set-up it could well turn out to be a shed load easier for the user but setting it up will require quite a steep learning curve. These tutorials are surprisingly clear English http://spoken-tutorial.org/list_videos I had expected a heavy accent but actually it's very comfortable to listen to. English and American are sometimes very snooty about this sort of thing but it's well worth trying out. There is a handbook now that can also help https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook The main thing is to work towards having the data tables in an external back-end, such as Postgresql or MariaDb/MySql but i don't think you have to do that to start with. I think you can export them later on. Then next time you want to build a database it'll be easier to start with the external one. At least that's my plan for what i'm hoping to do. Errr, i would hesitate to say this, if i was in your position, but if green is used for all the most recent recent cells how is the spreadsheet going to know when recent has eventually become old? An extra column to type the date into could be used to show when the latest change was made to that row. or maybe an extra date column beside each piece of data? I think i would go with 1 extra column for date and another for comments and the comments one to be used to say which bit if data got changed? Regards from Tom :) From: Tanstaafl tansta...@libertytrek.org To: users@global.libreoffice.org Sent: Friday, 19 July 2013, 11:44 Subject: Re: [libreoffice-users] Calc - allow users to format cells on protected sheets On 2013-07-18 8:17 PM, Errol Goetsch er...@xe4.org wrote: PS In this example, the trigger value is transparent when the cell is unlocked (the user can see the conditional formatting formula), but you can make the trigger refer offsheet to a value the user can't see. Hope this helps Thanks for trying, but no, it doesn't. Libreoffice has exactly two options under 'Allow all users of this sheet to: [ ] Select protected cells [ ] Select unprotected cells Excel has a whopping 15: [ ] Select locked cells [ ] Select unlocked cells [ ] Format cells [ ] Format columns [ ] Format rows [ ] Insert columns [ ] Insert rows [ ] Insert hyperlinks [ ] Delete columns [ ] Delete rows [ ] Sort [ ] Use Autofilter [ ] Use PivotTable reports [ ] Edit objects [ ] Edit scenarios We have always maintained all of our internal documents and templates in ODF format (since we started using Openoffice at version 1.0), and I'm trying to avoid changing the format of this file to .xls and start using Excel to edit it, because doing so will open the door to converting *all* of our internal documents and templates to excel/word format. This particular spreadsheet is a Master spreadsheet where we put all of our Sales numbers, and the boss wants the person assigned to update the sheet to start changing the BG color of the most recent changes. The problem is, the MAIN sheet that everyone looks at, references numbers that are entered on their Managers 'Team' sheet, and must be locked to prevent accidental deletion of the content. Crap. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calc - allow users to format cells on protected sheets
Depends on where you open it. If I double-click an email attachment to open it, I can't make any changes at all (not even column width). Once I save it to my hard drive, it becomes unprotected. I think what the OP is referring to is that in Excel you can do whatever you want to a protected spreadsheet -- you just can't save the changes. Thus, you can do a lot of what if analysis but can't mess up someone else's work. Dave On 7/18/2013 11:44, Tom Davies wrote: Hi :) I think if you open an Excel spreadsheet that is supposedly protected in Calc then you bypass all the password protections and stuff. My company's (well the place where i work, it's not really mine as such) finance lady was somewhat horrified when i was easily able to fix a problem for her despite having no idea what the password was. I hadn't even realised the spreadsheet was supposedly protected! Regards from Tom:) From: Tanstaafl tansta...@libertytrek.org To: users@global.libreoffice.org Sent: Thursday, 18 July 2013, 15:47 Subject: [libreoffice-users] Calc - allow users to format cells on protected sheets Hello, Microsoft Excel has the ability to allow users to apply formatting changes to protected cells when a sheet is protected. Is there any possible way to do this in Calc? Thanks -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Calc - allow users to format cells on protected sheets
On 2013/07/18 04:47 PM, Tanstaafl wrote: Hello, Microsoft Excel has the ability to allow users to apply formatting changes to protected cells when a sheet is protected. Is there any possible way to do this in Calc? Thanks Yes, using conditional formats. Protecting the cell _and_ the sheet together normally locks out all changes, but a conditional format can override both and unlock the cell (but not for formats alone). In the example at https://docs.google.com/file/d/0B6LXy9sguZVkeE8wTFgyZ2JQcW8/edit?usp=sharing 1. d2 and d3 are both Format Cells|Cell Protection|Protected (this is to show that locked cells can be opened) 2. c2 and c3 are both Format Cells|Cell Protection|unprotected (this is to change their value so as to trigger the condition) 3. sheet Protect is Tools|Protect Document|Sheet|Select unprotected cells 4. 2 styles exist; Protect, which is red background and cell protected, and Unprotect which is blue background and not. 5. d2 and d3 also have conditional formats. so that when the adjacent cell is a particular value (here 1), the Protect style applies, when the cell is not 1, the Unprotect style applies. The effect of this is that when c3 = 1 (or some password of your choice), d3 can be selected|format changed even though the sheet and d3 are protected. This way achieves the intended result, with the possibly unintended result of opening d3's value to change as well, which undoes the intention of protection. A half-way is to use conditional formatting to switch automatically between styles based on pre-set criteria, giving visual diversity without creating edit risk. PS In this example, the trigger value is transparent when the cell is unlocked (the user can see the conditional formatting formula), but you can make the trigger refer offsheet to a value the user can't see. Hope this helps Errol -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
