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

1+1=3

This is, of course, an extremely old trick in Excel which I use to try and generate some interest in spreadsheets when teaching at the sleepy 3pm slot.



The formula for F4 looks fine, so why on earth does it show the wrong answer?

It's all to do with the difference between what Excel knows and what Excel shows. The figures above have had their decimal display adjusted so the display is rounded to the nearest whole number. In fact the data in B4 and D4 is 1.4 and in F4 Excel calculates 2.8

Whilst this may seem either stupid to do in the first place, obvious or generally something you don't need me to tell you about, problems can arise when, for example, dealing with money. You may well not notice that costs from some other calculations, rounded to the nearer penny, actually add up to a different total than the figure Excel shows as a total. Excel will add the exact figures (calculating up to 15 decimal places) and then round the total to the nearer penny - not necessarily the same thing. In an RSA OCR spreadsheet exam many years ago this occurred and only one out of hundreds of students spotted it and asked me what to do.

If you prefer to have 1 and 1 making 2, and totals of displayed figures adding up to the same figure as you'd get from good old mental arithmetic, counting on fingers or even an old calculator, then you'll need to tell Excel what you want.

There are three useful formulae:
=ROUND() does what Excel does naturally when you change the decimal display, rounding to the nearest number of digits indicated
=INT() may be more useful, displaying just the whole number
=TRUNC() is the best option, restricting the number stored to a chosen number of digits.




You could now, for example add up column H or I and get 30 using the usual sum formula. It is important to apply the formula at the right stage of a calculation but this might help avoid less obvious but potentially costly or embarrassing errors.

London, because it's the least inconvenient . . . ?

Ten people trying to decide where to meet. Everyone has a good reason for not wanting to go somewhere as well as their own preferences. How do you decide? The boss says "London, because it's the least inconvenient" and he may well be right but it would be nice to see it in writing. Rather foolishly, I thought it would be easy to knock up a spreadsheet that would add up people's votes and work out what was actually the most popular, least inconvenient or whatever. Partly because I decided to include all sorts of checking things, validation and partly because I'd never used =AND before, it took ages!

No doubt the meeting's been arranged in London by now and it really needs to work on the web to do what I wanted but maybe all the effort will be of use to someone teaching Excel or trying to figure out some formulae. The sheet's protected but there's no password. Let me know if you use it, have a better idea or spot errors.

It's a small file that you can get here. There's an on-line version that actually seems to work using Google spreadsheets. Amazing! As I might really use this I'll just give the team the address for now but will release the link genrally soon.