I've created a proof of concept (PoC) to test on users for a project. The wookbook is made up of a number of worksheets - which refer to each other - with LOOKUPS throughout.
I want to share the PoC with a prospective user but don't want to share the full spreadsheet file and only want them to be able to add to certain fields to test functionality. They will add text to certain fields which will then refer to different parts of the spreadsheet and make calculations based on those inputs.
If I publish as a web app they will have full access to the formulas (my secret sauce) which I don't want.
Someone suggested Google sheets. I've tried importing to Google sheets and publishing there but none of the formulas port across and as such none of the buttons I've created work.
Can anyone help?
Here's what I would do. First, click the select all arrow at the top left of your sheet, right-click somewhere on the sheet, click Format Cells, then Protection, and lock and hide everything. Now go to your sheet and click the individual cells you need unlocked for testing purposes (perhaps color them a different color as well). Now lock your sheet.
A word of warning, though. Worksheets can easily be unlocked by someone who knows the workaround (or someone willing to spend ten minutes on Google to find it). If possible, turn your formulas into UDFs (they're typically reusable that way too, so it's an added bonus), and then lock your code down. You can also convert them into an add-in, making them a bit more secure as well.
Ultimately, I don't believe a web app is a viable option if you don't have the skills or know-how to build one. Excel is extremely weak when it comes to protection, and if you're not willing to trust your consumer and/or not willing to make your formulas insanely complicated (to a point where they're near impossible to breakdown by anyone other than the creator), then you need to rethink some things.