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!)

Internet Explorer 9 beta available

Well it certainly looks pretty. Internet Explorer 9 that is. You can download a beta version from here if you have Windows Vista SP2 or Windows 7. And you do need to download the Vista Service Pack 2 before it will work. I could add what on earth are you doing using Vista but I won't. Oh, too late. Note the url - I wonder how much they paid whoever first registered that!? Anyway, as someone who only now uses Internet Explorer to work on moodle VLE pages and who has to tryreally really hard not to use bad language when forced to use it on classroom pcs, I can only hope that it's as quick, as intuitive and pleasant to use as Chrome.

I'll let you know in a while . . .



I presume it will be labelled IE Ja in Germany.

Reserve your name at about.me

A bunch of clever people bought the domain about.me which is probably about the only .me worth bothering about. They've now come up with a nice idea that they propose will combine multiple online profiles scattered across various services, including Facebook, LinkedIn, Flickr, and Twitter. You register your name and get the page about.me/yourname which will feature all that's happening across your sites and profile pages.
As they say: "... and one problem we face is pulling all of this information together to build a single on-line identity — be it for personal use, or to create a professional on-line profile. Our focus is simple, enable you to:

• create a personal, dynamic profile page (think splash page) that points users to your content around the web (versus depending on Google search); and
• understand how many people see your profile, where they're coming from, and what they do on your page."

Their investors include True Ventures, Radar Partners, Freestyle Capital, Scott Kurnit, AOL Ventures, David Mahoney, Founder Collective & Ron Conway.

It may or may not develop but it won't cost anything to register your name. Remember how you wished you'd got on Facebook, Twitter etc. earlier before someone else had grabbed the name you wanted and left you thinking up something that hadn't already gone? OK. Don't say I didn't warn you! I've got mine.

Creating a blog and using it for an on-line portfolio