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

If you have to use Facebook, you need Fluff Busting!

I do have to use Facebook sometimes. Thank heavens for the excellent 'clean-up' tool that F.B. Purity (a chap called Steve) have developed. F.B. stands for Fluff Busting and it simply gets rid of all the annoying rubbish including the Timeline.

There are so many features that I am going to cheat and simply paste what is listed on the F.B. Purity site. I'm sure Steve won't mind, and it ensures I don't get any bits wrong. I did correct some apostrophes though but I have yet to meet a really smart programmer who also can do apostrophes and I'd prefer he carries on developing the former skill!

All this is free! It's so worthwhile, though, that I made a donation and would urge anyone that can afford a pound or two to do the same. You do need to stop using Internet Explorer, though, to get the benefits. It runs on Firefox, Chrome, Opera and Safari but does not work on mobile phone browsers yet.

F.B. Purity can be used to display the Timeline in a single column

Hide "Read an Article" / "Trending Articles" / "Trending Videos" / Facebook Actions + Frictionless Sharing App stories

If you are not interested in what news articles others are reading, FB Purity gives you an option to hide these types of stories; this option also hides all the other FB Actions aka "frictionless sharing" app's stories, such as "watched a video" "read a book" "pinned something to pinterest" etc.

Fixes news article links that redirect via Facebook applications
FBP has an option for fixing the news article links that redirect via Facebook applications, and instead lets you go directly to the news article, this means what you read won't show up in other people's tickers or newsfeed, and you wont get hassled with the news organisations' application page wanting you to add the application every time you click a news article link.

Hide Facebook Questions
FB Purity has an option to hide other people's Facebook Questions and answers from showing in your news feeds

Restore Facebook's old Comment system, where pressing Enter or Return adds a new line
By default FB Purity reverts Facebooks comment system, back to adding new lines to your comment when you press Enter or Return. If you dont want this, you can disable it in the FBP Options screen.

Lets you force Facebook to sort your newsfeed by "Most Recent" stories first
Although Facebook has an option to let you choose what order you want your newsfeed sorted in, it seems to keep "forgetting" the user's preference. This option lets you force Facebook to always show you the "Most Recent" stories first.

Hide the News Ticker / Hide the Chat Interface
F.B. Purity adds options to hide both the new FB Chat interface and also the "News Ticker" aka the "Happening Now" sidebar, which is the box of constantly scrolling news in the top righthand side corner of your page.

Change the Font Size and Font and Background Colours
Facebook changed the font size really small on the homepage / newsfeeds, luckily FB Purity now has a font size feature, to access it, you just need to go into the F.B. Purity settings, by clicking the F.B Purity link above your Facebook homepage.

You can also choose what colour scheme or colors you want to view facebook with, you can enter any colour name or HTML/CSS colour code on FBP's options screen, and then Facebook will be displayed in your chosen colors.

Application Whitelist
The application whitelist lets you specify particular applications, whose messages you *do* want to see on your homepage. The script comes with a default list of applications that will not be hidden by the F.B. Purity app filter.

This list includes the following applications: FB for Iphone, FB for Blackberry, FB for Palm, FB for Android, Tweetdeck, Digsby, Tumblr and Selective Twitter.

For instructions on how to add applications you *want* to see to the list, see the F.B. Purity Application Whitelist Guide

If you do want to see all application messages in your stream, you can turn off the app/games filtering in your feed.

Extras Blacklist
By default the script hides the following "Extras" (though you can choose to see them if you wish):
Liked (Became a Fan Of)
Joined a Group
Became a Friend of
Is Attending an event
Attended an Event

Additional optional "Extras" that can also be hidden, but are shown by default are:

Facebook Questions (and answers), Created an event, Changed location (FB Places), Updated profile, Changed profile picture, Changed relationship, Commented on or likes status, Uploaded photo, Shared Photo, Tagged in, likes or commented on photo, Tagged in, likes or commented on album, Uploaded video, Tagged in video, Share a link, Commented on or likes link, Read an article, Trending articles, Posted a note, Tagged in note, Commented on wall, Commented on group wall, Commented on page wall, Commented on Event wall, Comments/Likes on "Top News" feed, Fan Page Stories, Recent Activity (on profile pages)

If you would rather see one, some or all of these messages, You can edit the default list, to your taste. Instructions on how to do this are here:
F.B. Purity Extras Blacklist Guide

Ice Cream Sandwich on Xperia P, thanks to Sony Support

Yippee! Finally got what I think is Ice Cream Sandwich on my Xperia P phone. The microSIM finally got recognised by the way - sometime on the road between Astcote and Milton Keynes the following day so that was one problem resolved. Getting the new software, though, was not so simple.

After a couple more tries at using the Sony PC Companion software I gave up and dashed off a reasonably polite note to the Sony support e-mail. All credit to whoever dealt with it, a couple of days later they accepted that Sony PC Companion wasn't going to work and even seemed to admit that the required software wasn't accessible immediately if one selected the Xperia P on their site! Instead, he or she suggested that I use W205 as my phone at the first stage and see what the Java powered window comes up with. Below are the instructions they sent:

Currently the program is not listed on the downloads section for your Sony Ericsson Xperia and so we would suggest that you select the 'W205' on the Sony Xperia website to be able to download the program. This can then be found by following the below steps.

1. Go to www.sonymobile.com

2. Select Support

3. Choose a handset from the drop down list (W205)

4. Select "View All Software Updates"

5. Update Service

6. Check the requirements and then "Download Update Service"

Once you have installed the Sony Update Service you will need to launch it. Once it opens simply follow the instructions that you will see on screen. The update should then begin.

The Update Service is about 34MB and arrives quickly enough on a PC. The operating system download is ten times that - about 340MB - which takes a while but it installs pretty quickly on the phone. There's that nerve-wracking moment when a low resolution O2 image appears a few seconds after switching on and seems to linger just that little bit longer than you would wish it to. Then it's all go and, with a dark Google search bar and some slightly different application motifs and then a steady stream of application updates.

Contacts, recent calls and messages are all intact but I expect I'll find something missing but nothing that can't quickly be retrieved I'm sure.

So all's well. The system seems very quick but the large RAM on this phone may be as much to account for that as the new operating system. Just need to look out for Jelly Bean now!

Oh dear O2. Simple questions need simple answers.

My Xperia X10 has been great but lately the need to charge it more than once a day and seemingly random shut-downs when it had way more than 50% charge was getting on my nerves. In a way I am quite glad that I didn't switch my O2 contract to paying a low monthly amount and keeping the phone which is what I probably would have done when my 2 years were up in April. These last few weeks convinced me that I do need another phone, well a battery probably would have done, but the Xperia P looked really nice, is due to get Android's Ice Cream Sandwich now - maybe even Jelly Bean soon - so I set up an upgrade through MyO2 on-line.

That was simple enough and the next day the new phone arrived. In the box was a SIM card which is a two-in-one affair which looks as though it would fit the normal size SIM socket as well as the new microSIM socket. You can break out the microSIM for phones like the Xperia P. As I couldn't transfer my old normal size SIM to the new phone I was wondering how on earth O2 would know that the new SIM was mine. And, presumably, it would have a new number. I suppose if it had been a really really nice new number I might not have minded but the prospect of having to update so many things with a new number wasn't that appealing. A colleague also pointed out that none of the people I'd given cards to or met at various places and scribbled the number down on bits of paper, napkins or hands would ever be able to know the new one.

So, rather than break out the microSIM and stuff it in and hope for the best, I got in touch with O2 via their on-line chat thing. I had tried communicating with Ask Lucy on their site but firstly she only wanted me to type one sentence at a time and secondly appeared just to send me links related to various words she recognised, all of which were useless. The chat worked better and I did get the feeling that 'Diana' understood my query. She said she would send a microSIM in the post which I should use. Well, that seemed simple and I assumed that she would somehow link that one to my account so that when I stuffed it in the phone it would recognise me.

It arrived very promptly the next day, today, and I set about getting the new phone up and running. The fact that I couldn't make calls didn't trouble me as it is a lousy area and that's often the case. GMail and Facebook were almost instantly up and running which was very encouraging. So far so good, I recall posting on Facebook at the time.

Then it told me that a system update was available. Yes, Ice Cream Sandwich already, I thought. Cool. So I plugged it in to the PC which set about downloading the new software. That took ages to get it on the PC and then it was time for the next stage: putting it on the phone. I followed all the instructions but each time it failed so I gave up on that. Try again another day. Over at the PC there was no chance of it getting a connection but putting it back next to the old phone, which did have a connection, I began to wonder about that recognition thing again. Looking in the applications I found the one that would tell me what my number was. No number. Hmm. I looked at Network Provider. None there. It could see O2 was there but wouldn't link me to O2. More or less as I had wondered at the very start.

Back to O2 support on-line. And that's where I spotted something called a SIMswap page. It was billed as the place iPhone users getting the latest iPhone could order these new microSIMs that it said they'd need. Reading on there was a link to a SIMswap page where you entered your number and, although it didn't say so, it looked like the sort of place that would do what I wanted.

I put my number in, entered the code they sent to my (fortunately still going) old phone and that may have done the job. I then got a message on the old phone to say that the switch would take anything between 30 minutes and 24 hours. I would know when it had been effected when my old phone stopped working, the site said. Now, I have no idea when that will be but maybe when I wake up tomorrow I'll have a dead old phone and a live new one.

It'll be fun finding out. But, really O2, you could have made that a whole load simpler by just adding a short paragraph in the box to say "Hey, you've got a phone that needs a microSIM. Your old one won't fit. Stick the new one in and go to our site etc..."

Watch this space. I still have the system upgrade to do!

Lots of monitors in one with Dextop

Dexpot turns your monitor into up to 20. Dexpot creates additional workspaces you can quickly switch between. Take, for example, Desktop 1 to listen to your favourite music, surf the web on Desktop 2, use Desktop 3 to handle your daily word processing or switch to Desktop 4 for a game of Solitaire.

Different icons, wallpapers, start-ups, and much more can be configured for each desktop.

Users owning netbooks, tablets and UMPC with small screens and low resolutions will benefit from virtual desktops as well.

Download at FileHippo

Make sure your pc isn't infected with DNSChanger before Monday 9 July.

It takes just a few seconds to use the free McAfee tool to check that your pc isn't infected with a nasty bit of malware that affects the way it locates internet addresses. This is from PC Advisor magazine:

The FBI estimates that as many as 275,000 PCs are still at risk of losing access to the Web on Monday when it pulls the plug on the DNS servers it has maintained to redirect PCs compromised with the DNSChanger malware to legitimate websites.

Read more: http://www.pcadvisor.co.uk/news/security/3368412/there-is-no-excuse-for-still-being-infected-with-dnschanger/?cmpid=HTML-DN060712&olo=daily+news#ixzz1zx6tF8Jz

Chances are you're fine and if you have a decent and regularly updated protection system running then it should be keeping you safe but that sentence starts chances are... Make certain this weekend. Click this link or others available in their article and get the satisfaction of knowing that you have nothing to worry about. Remarkably, McAfee aren't even using this to try and sell us anything and I was pleasantly surprised at just how quick and ad-free the check was. So do it now. And relax.

View your sites as those with iPhones or iPads do - free emulator

Although I am still firmly in the happy Android camp, I do appreciate that very many people indeed have iPhones and that they may well be visitors to sites that I design for clients. Until now I have had to ask my daughter to look at sites and let me know if there are any problems but I've just discovered a little bit if software called ibbdemo3.

You'll need to have the latest version of Adobe Air but this is useful anyway and free. Then download ibbdemo3 and you can check out web addresses on a Windows machine. The iPhone / iPad emulator works a treat and could save a lot of time and errors!

Pressing Ctr1 will also display as an iPad and Ctr→ switches between portrait and landscape. Great!