Member of the Month

partyandBS
December, 2024

Online

Welcome to the Newschoolers forums! You may read the forums as a guest, however you must be a registered member to post. Register to become a member today!

Microsoft Excel Help

profile picture
ObiWanKablowme
Posts: 421 - Karma: 539
so my roommate has created a database in excel that he wants to be able to let clients use. however, he cannot figure out how to keep those clients from seeing his formulas. he has tried using a series of workbooks (A, with the formulas stored in A, to B to C), so that the client never has immediate access to the formulas in A. but for the data to flow across the workbooks all three must be open which obviously won't work. he has obviously also tried locking it but a simple copy and paste of the workbook into a new workbook can void the lock. so I'm wondering if anyone knows of a way to let someone access a workbook without seeing the original formulas. +k for help
Oct 25 2013 11:30PM
2 0
profile picture
TechnoPotamus
Posts: 11626 - Karma: 3,522
i would have thought simply locking the cells or locking the worksheet would do it
Oct 25 2013 11:31PM
0 0
profile picture
ObiWanKablowme
Posts: 421 - Karma: 539
nah that's a bug with excel it only works if you don't copy and paste. which means that he wouldn't be able to give it out to clients
Oct 25 2013 11:37PM
0 0
profile picture
t_rob
Posts: 6433 - Karma: 11,459
If you want to keep the inputs editable but lock the formula:

1. select the cell and lock and hide it with format cells.
2. Protect the worksheet or workbook and check select unprotected cells and format cells.
Oct 25 2013 11:49PM
0 0
profile picture
no_steeze
Posts: 23992 - Karma: 4,273
Can't you lock it in read only so they can't even copy and paste the cells? Usually a simple copy without using paste special won't reveal the formula, it'll just copy the cell values without any formulas
Oct 25 2013 11:53PM
0 0
profile picture
paige.
Posts: 1932 - Karma: 926
Yeah he could import it into Microsoft Access quickly and easily and then just send clients forms that then get integrated back into the main database once they fill them out. That's pretty much what the program is designed for.
Oct 26 2013 12:46AM
0 0
profile picture
EASTIE21
Posts: 278 - Karma: 21
Copy > Paste Special > Values That is the most basic if you only want to show the value of the field instead of the formula used to create the field.
Oct 26 2013 7:59AM
0 0
profile picture
ObiWanKablowme
Posts: 421 - Karma: 539
in read only the clients wouldn't be able to add in their own data
Oct 26 2013 9:06AM
0 0
profile picture
ObiWanKablowme
Posts: 421 - Karma: 539
this is a decent idea thanks
Oct 26 2013 9:07AM
0 0
profile picture
first_rodeo
Posts: 2385 - Karma: 7,781
MS Access
Oct 26 2013 10:15AM
1 0
profile picture
Barefootin_Fiend
Posts: 7618 - Karma: 4,727
you need to protect the worksheet. in the protect options, you can unclick the default checked options that allow users to select protected cells. therefore, they'll be able to see the output, but cannot put the cursor over the cells with formulas in them to steal the base formula, edit it, etc. that will also prevent them from copy and pasting in to a new workbook.

i spend 8-10 hours a day in excel, so hit me up if you have any other questions. /excel nerd.
Oct 26 2013 11:28AM
0 0
profile picture
Barefootin_Fiend
Posts: 7618 - Karma: 4,727
i should clarify, you'll first need to highlight the cells you want to prevent them from seeing the formulas in, right click, format cells, select protection, click on lock cells. then when you protect the worksheet and as you go to enter in the password, there will be a list of a bunch of options you can check or uncheck, the top one or two is "select locked cells" or something like that. uncheck that.
Oct 26 2013 11:31AM
0 0
profile picture
ObiWanKablowme
Posts: 421 - Karma: 539
thanks that sounds like a solution +k
Oct 26 2013 2:17PM
0 0
All times are Eastern (-5)