Forum Discussion
How to lock a spreadsheet in Sharepoint
I need to lock a spreadsheet to be view only in Sharepoint. There has to be an answer. I don't want to make it by permissions or change library settings, just to lock or encrypt a spreadsheet. Ple...
I may be missing something but, it’s Excel, why wouldn’t you just use the Excel protection functionality that’s been there for 100 years?
- You can actually achieve this using a drop-down list.
Select the range C3:C50 then select ‘Validation’ from the Data menu > Select list in the Allow option drop-down > click in Source Control and select the range, on the other sheet, which includes your list of vendors (you can also choose here whether you want to show the drop-down arrow in the cells so you can choose the vendor from the list - check the In-Cell Drop-down box).
Note: If you have two entries which start withe same letters, you will not be given a suggestion for the entry until you reach the first unique letter..... e.g. in a list of countries containing United States of America, United Arab Emirates and United Kingdom; when typing in United Excel can’t differentiate between the three, only when you type the first letter of the second word, I.e. the S of States, would Excel offer auto-fill for USA. Kathy -- someone else may have a more direct answer (more satisfying)....So far as I'm aware, there is no FORMULA or FUNCTION for what you're asking. There conceivably could be a macro or VBA routine, but that seems like overkill at this point.
If I were sitting down with you face-to-face, I'd have a bunch of questions before we went any further. Such as
- What is the bigger picture here? What is the purpose you're working to serve?
- What else are you expecting on sheet two, once "Pepsi" gets filled in? Are you going to be adding data about "Pepsi" or are you wanting data to be retrieved from somewhere? Where?
- Will "Pepsi" (using it as a stand-in here for any name, just as you did) ...will "Pepsi" being appearing only once on sheet 2, or is it possible it will appear multiple times? If the latter, what will be different about the multiple occurrences?
Why ask all those questions? Well, it's not that I'm nosy. Really. But how we approach answering your very first question might change depending on that bigger picture, the ultimate purposes, etc.
mathetes Just looking to fill a sheet with vendors every week that we paid. So this week I paid 20 different companies. Next week I paid 30.......some the same as last week. Just the name on the sheet and that is it. I just don't want to have to keep typing every single vendor name each week on a spreadsheet. If there is an auto fill or something....
If that's really ALL you're doing, just a list of names and you start over each week with mostly the same names, then a simple solution is to keep it all on one sheet. Excel is generally "smart enough," when you're using a lot of the same names (same texts) and repeating them in the same column, to prompt you with the complete name once it recognizes a unique set of letters. Try it.
And you can just enter the week (the monday or friday of whatever) once and copy to the other entries for that same week.
But just to underscore here, you're really not using Excel for any special purposes, using any of its data analysis purposes or anything like that, right? If you are, if at the end of the year you want to know how many times you'd paid "Pepsi" for example, we'd possibly want to get more sophisticated.
Hi BradD,
I tried the sharepoint sheet to be edited in the excel first but it doesn't go to the excelsheet itself, it only reopens in the sharepoint link, I used the "OPEN IN DESKTOP" option, still it reopens/refreshes my sharepoint sheet only and thereby not giving me much option to move further ..
BradD thank you for the suggest that is 100 years old. This is a document created in sharepoint and lives in sharepoint and it does not have all the options that a desktop Excel document has.
Do you have another suggest that maybe isn't as old as the first one? I really am looking for a solution.
SheriW but regardless, again, it's still Excel. I just tested it myself.
I created a new workbook, opened it in the desktop app, locked it (and it saved automatically) and then when I go back to the online view, it's locked and I cannot edit it. You can't edit the protection in the online view but that doesn't stop you from opening it in the desktop app and making those changes.
If that's now what you're after then I'm clearly misunderstanding your issue.
