Help with Google Spreadsheets?

Right, here's the problem.

People fill in some data on a Google Form which is automatically collected in Sheet1 of a spreadsheet. Some formulae are applied as it comes in and some results are calculated. On Sheet2 of that spreadsheet there is a cell where someone can type their name and a simple LOOKUP formula gets their result from Sheet1 and displays it.

The real data and formulae are vast and quite complex but the essence can be simplified in this example:

Sheet1: responses and the calculated 'feedback'

Sheet2: an example of how a respondent could see their result

So far, so good. I can put a link to the sheet at the end of the survey. But...

Problem #1

Users would need editing rights in order to be able to enter their name. How can I publish the spreadsheet but only give editing rights to one sheet.

Problem #2

Users can see (and edit!) Sheet1 too which could be disastrous. How can I hide Sheet1 without their editing rights being able to access Unhide from the menu?

Problem #3

Even if I can restrict users to seeing only Sheet2, there is a danger of their messing up the formula in D4 that shows the result, rendering it useless for the next user. How can I protect everything except cell B4? (The 'protect range' instructions seem very confusing indeed in practice!)

Problem #4

Assuming that one or other of these problems isn't readily soluble, how about having Sheet2 accessible as a totally separate file so that there would be no access to the source data and other people's data? In Excel it is possible to link to data across different files but how can you do this in Google Spreadsheets? There is something called importrange which did collect and display the data from one file to another but it didn't collect the formulae so no matter how often a user tried to enter another name in the sample above, he or she remained Fred (or whoever had been displayed when importrange was run).

I am sure that there's a script or some way to get this working but it has escaped me for a day or two now. So over to anyone out there who can think of a nice solution. 

Here is a link to the simple sample if you want to work with it. (Make a copy - or several people working on the same file could get confusing!)

Plug and go go

With a computer on its last legs and a laptop down to its last GB of space I've got some new equipment coming. That's nice but how on earth do I move all the files from one to another? External hard drives would work but less than £10 got me a USB Data Link cable from Gizoo. It's great, plug one end into one computer and the other into another and you get a split screen with the local files in one part and the remote machine's in the other. Moving stuff is just a cut and paste job and several gigabytes that would have needed several trips by normal USB stick were almost instantly transferred.

Another gadget I got is a Pama bluetooth hands-free box. Intended for the car, as I seem to get more calls when I'm driving than I do when I'm not, it is a slightly revised version of something I bought a couple of years ago. That refused to recharge for some reason I never discovered and, as it had been a bit scratchy and basic I didn't bother getting another. This version is much clearer and displays caller ID too which is useful. Only about £30 from Amazon and it also has another use - I live in an area of poor mobile reception with none anywhere inside the house where I can sit in any comfort. With this device, the phone can be placed wherever the signal's good and I can just chat to the box in the warm and dry. Few of you will appreciate the difference but those that do should take a look. It's cheap and works.