Create a list box in Excel to ensure valid data entry

I’ve been pulling together a spreadsheet for tracking and planning the expenses of my upcoming wedding and honeymoon. Having not used Excel for a while, I was a bit rusty. Anyway, what I wanted to do was have a simple list-box in a cell from which I could select ‘Yes’ or ‘No’ (I know, not earth-shattering at all).

So, I had a quick Google and found a plethora of tutorials on how to add a combo-box or a list-box; but, they all got way too messy and created a box that sat somewhere, and referenced a list of values before returning the index of that value, and then you could use that index to read the original list of values and select the correct one in order to use it. Phew, that was tiring just typing it!! Not a happy camper, but I knew there must be a simple way to have it “just work”.

I got there in the end, after some muddling around, and here’s the solution using Data Validation (this just does ‘Yes’ or ‘No’, but it’s easy to add more values):

1. Select all the cells you want to have this validation applied to
2. Go to Data | Data Validation
3. On the Settings tab, select List from the Allow drop-down list
4. In the Source text box, enter the valid values for the data, separated by commas (eg: Yes, No) [note: don't use quote marks]
5. Select the In-cell Dropdown check box
6. Click OK

Done!
When users move to one of the selected cells, Excel displays a drop-down arrow. Clicking the arrow reveals a list of valid options which users can select for that cell. If users attempt to type an entry that’s not on the list, Excel will display an error message.

More Microsoft logic FTW!!

Sometimes Microsoft’s logic impresses me even more than normal. To quote from the Microsoft Support pages:

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

Let me highlight that for you there:

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

asp:DeclarativeCatalogPart WebPartsTemplate must have odd number of elements

I discovered an interesting anomaly today, working with ASP.NET and Web Parts, I had an issue whereby creating a CatalogZone caused the HTML generated from template to lose a whole lot of closing tags. This threw the whole page out the window as the CSS was stuffed. I did a LOT of Googling to no avail.

Finally I went into the tried and true method of trial and error; removing one element at a time until I found the problem bit. This was also anomalous as I have three pages with essentially the same code and only one breaks! I won’t bore you by going through every iteration and step I took, I’ll just bounce to the solution.

In their wisdom Microsoft has apparently made the template work for an odd number of elements, but an even number has a tantrum and is not composed correctly. It was only when I realised that that during my trial and error, the two working pages had 15 and 5 elements respectively, and the “broken” page had 6 elements; thinking this could not possibly be the issue, I checked and was astounded. The general response from my colleagues was “you’ve got to be f@$#en kidding me”. I swear, I’m not.

In summary, from an HTML point of view, this works:

<asp:CatalogZone ID="CatalogZone" runat="server">
	<ZoneTemplate>
		<asp:DeclarativeCatalogPart ID="DeclarativeCatalogPart" runat="server">
			<WebPartsTemplate>
				<tfr:stuff1 ID="stuff1" runat="server" />
				<tfr:stuff2 ID="stuff2" runat="server" />
				<tfr:stuff3 ID="stuff3" runat="server" />
			</WebPartsTemplate>
		</asp:DeclarativeCatalogPart>
		<asp:PageCatalogPart ID="PageCatalogPart" runat="server" />
	</ZoneTemplate>
</asp:CatalogZone>

and this doesn’t:

<asp:CatalogZone ID="CatalogZone" runat="server">
	<ZoneTemplate>
		<asp:DeclarativeCatalogPart ID="DeclarativeCatalogPart" runat="server">
			<WebPartsTemplate>
				<tfr:stuff1 ID="stuff1" runat="server" />
				<tfr:stuff2 ID="stuff2" runat="server" />
			</WebPartsTemplate>
		</asp:DeclarativeCatalogPart>
		<asp:PageCatalogPart ID="PageCatalogPart" runat="server" />
	</ZoneTemplate>
</asp:CatalogZone>

Counting unique values in Excel

The following formula counts the number of unique values in a given cell range

=SUM(IF(FREQUENCY(IF(LEN(B2:B16)>0,MATCH(B2:B16,B2:B16,0),”"),
IF(LEN(B2:B16)>0,MATCH(B2:B16,B2:B16,0),”"))>0,1))

[** Note: replace the B2:B16 range to be whatever your required cell range is]

To calculate, DON’T press ‘Enter’, instead press ‘Ctrl’ + ‘Shift’ + ‘Enter’ and you’re done.

Tah Dah!!

Apple keynote, Google CodeJam, DNS dramas

OK. The DNS issues are now sorted; it turns out that the settings at the NIC end had not propagated out correctly and that took another 24+ hours to remedy. Anyways, I’m now able to get some testing and such started on the live site. At least I was able to FTP all my files via the IP address in the meantime; so, no harm no foul. Nice learning curve anyway getting to play with “normal” web-hosting and dev issues.

Apart from that, the Apple “It’s only Rock n Roll, but I like it” launch was a bit underwhelming I have to say. So we now have an iPod Nano with a camera, but we still don’t have an iPod Touch with a camera??? And, I’m sure Apple has a plan, but most of their clients are right-handed and they put the camera on the back lower left; ie just where a right-hander’s hand will be whenever holding their Nano. I dunno there, maybe Apple R&D is just full of lefties?? Hmmm. Although el Jobbso did discuss this a little with the NY Times, and indicated that they are seeing the iPod Touch as a game platform instead of an iPhone without the phone (an “i” ?), and that as they want to reduce the price-point for it they don’t want to add more hardware all the time.

Google has launched a new, bigger search box!! You can see a comparison pic here, as well as a timeline of the Google homepage over the years here. I see a movie in the making: “Search Wars”, or maybe “The Pirates of Mountain View”, or even “Google – the pre-Bing years” as a tele-drama. It’s going to be interesting to see how the Microsoft/Yahoo! changes the search market with Bing.

I’m still waiting to hear what’s up with CodeJam for the next round. I haven’t had an email, or been able to find any updates on the site, but I’ve now emailed asking as Round 1 begins in 48 hours or so from now. No stress, but it would be nice to know when I’m actually competing. I’m hoping for Round 1A, as that is at 13:00 Saturday for me, as opposed to being at 04:00 Sunday (not a pleasant thought!!). [Update - just as I went to post, Google has replied to me already. They are letting everyone compete in all three sub-rounds until they qualify. I like prompt service!!]

[Updated update!! - It turns out that the info above regarding Round 1 has not yet been released! So, you heard it here first. Google will be announcing this and emailing it out to qualifiers in about 24 hours time. YAY!! My first exclusive!!]

No YouTube for you today, but I will again tomorrow. I guess the best thing for you to do instead is to go check out engadget live from the Microsoft Windows Media Center event. Go to it!

the weekend has landed

I didn’t get to blogging yesterday, as I was working hard on assignments. You may have noticed the quick post I put up with some music and a video of it. Check it out if you get the chance, Bob Marley is awesome and the remake is brilliant. Just gets me all summery and wanting the sun and sand!! Back to today now, I had the dedication of some friends’ baby this morning, Annabelle is SOOOOO cute it’s ridiculous. Also bumped into some other old friends there whose baby was being dedicated in the same service. Great service, great people, and lots of cute babies. And then there was food afterward. I also started up a page on here linking to useful websites for coding, web-development, anything scripty as a kinda go-to page for quick reference. Hopefully it becomes useful for some of you. Check it out here.

In techy/g33ky/web land, the DDoS of Facebook and Twitter the other day is now being treated as an attack by Russian hackers, specifically targetting a 34-year-old economics lecturer and blogger who criticises Russia’s military presence in the Caucasus. Facebook and Twitter launched investigations yesterday, as a blogger called “Cyxymu” (a latinised version of Sukhumi, the capital of the Georgian breakaway republic, Abkhazia) announced he was the attack’s first target. Max Kelly, Facebook’s chief security officer, confirmed Cyxymu was the target.

Just checked my Twitter account and found that none of my text Tweets this evening from work have made it through. Thanks Vodafone, you’re awesome!!!!

Rumours are starting [via Engadget] that iTunes 9 will have BluRay support. As Engadget says

To be fair, the HD disc format wars are all but over at this point, and the most recent Final Cut Pro actually lets you burn video directly to a third-party BD drive, only to have to play the discs on another, non-Mac device. This is all pretty sketch at the moment, and we doubt the boys in Cupertino will be showing their hands until just after the eleventh hour — let’s not forget, also, that iTunes is also available for Windows which does have other third-party Blu-ray playback software.

So, I guess we’ll just have to wait and see now, won’t we??

Gizmodo is also showing off this 16GB USB, plus whatever microSD cards you shove inside. Kinda Osum!! [geekstuff4u via ohgizmo via gizmodo]

Into a night of playing with Microsoft Virtual PC and the WRK for Windows Server 2003 now. Ciao.

a new semester begins

Today is Tuesday (duh!), and uni started back yesterday. I am lucky enough to start my week at 8am on a Monday, so no easing into the lecture schedule for me. I was pleasantly surprised to find that one of my papers has already released the first assignment. This means that I will actually be able to get onto it in a timely manner. Normally they all wait until third/fourth week to all release them at the same time, and then are all due at the same time. I’m a bit sick today, so I’ll be starting on this assignment tomorrow and trying to get it done by the end of the weekend.

Also discovered that one of my other papers get to play with Windows source code (WRK). Very cool!! It’s only the WRK for Server 2003 but, as they say, that’s pretty much the same as XP, which is almost the same as Vista; so there we go. My schedule isn’t too bad for my – touch wood – final semester before returning to the real world, so I’m not stressing over possible time issues at all. I’m doing mainly CompSci papers to finish off, but most of them are network related, so I’m really weighing towards mainly InfoSys in reality. I’ll also be completing my CCNA semesters 3 & 4 as part of one of my InfoSys papers which is a bonus. And, I believe my other InfoSys paper allows me to sit an Oracle certification at the end too. Look at me the uber-student.

Check this link out for a bit of a laugh when you get the chance (make sure you have sound!). It is safe for work. Also, notpron.com is classic. It’s also safe for work, but the url my cause filters to trigger, so best to do it at home. I have to put in the following video, just because I hadn’t seen it for ages, and I re-watched it again over the weekend. It’s still as ridiculously funny as it was when it first came out!!

As you’ll soon see, I’ve been going through my favourites folder on YouTube, and I went right to the very back end for the oldest (possibly forgotten) vids I could track down. Here’s Best Baby Break Dance:

and this one of a guy catching glasses with his face:

I’ll end the queue of videos with this one. It’s actually quite heart-touching, so enjoy and be happy for him:

I’ll be doing some more tuts this afternoon, or tomorrow. Something to do with XML and HTTP now that I’ve had a bit of fun setting that up for my site (launch will be within the next two weeks I think!!) and coordinating it with JS so that other sites can call to mine and get responses back. I’ll also look at something to do with dynamic JS creation from PHP.

back to a normal life

Getting back into the swing of things is always a struggle after a break. Just been reviewing my progress on my sites, and planning the next few weeks of work on them before diving back into it. Also, chasing a couple of jobs on GAF to score some dollars if possible. No luck yet, I have to bid too high most of the time and the Indian web-dev shops undercut constantly. It’s still good practice, because I try to plan out solutions and the jobs give me inspirations anyway.

I’m starting to do a bit of a ferret re the upcoming Google Chrome OS, see what I can come up with as an application for release next year. It’s got to be putting the jimminies (don’t know where that word came from) up Microsoft, because it’ll be yet another OS that demonstrates how badly worked Windows is, but let’s not go there now.

Here’s a cool ad for a hospital, but make sure you don’t judge things too quickly.

I also decided I wanted to share some inappropriate URLs, and so I’ve been rummaging around and pulled out some of the most classic accidentally inappropriate URLs of all. I’ve seen a list of some of these years ago, and it’s probably on my computer somewhere, but here it is on the web:

• therapistfinder.com – helping you find therapists; and also therapistschoice.com – providing you with all your therapy equipment needs
• expertsexchange.com – exchanging the views of experts; I actually used this for a bit, but didn’t want to continue paying the money for it; they’ve now moved to experts-exchange.com
• molestationnursery.com – one of my favourites, it’s a plant shop located at Mole Station Island in Aussie; they’ve now moved to molerivernursery.com
• whorepresents.com – celebrity management
• penisland.net – for when you need a pen
• powergenitalia.com – they power Italy; it’s been “under constructioned” now, but you can see the archived version here
• ipanywhere.com – yep, give your computer tech all the freedom they need
• cummingfirst.com – it’s a church, well at least XXXChurch.com was deliberate
• speedofart.com – artists/graphic designers (this one’s for you Shaun!)
• gotahoe.com – what can I say??
• muffdivingclub.ie – hmmm, there is a town called Muff in Ireland – from the site’s home page:
  

  We have our members area that allows you to post your blogs, join the Muff Diving Club, upload pictures of you and your friends Muff Diving.
  We’ve also introduced a secure, online shop that allows you purchase everything from you own Muff Diving T-Shirt, to personalised mugs, hats, etc.

I also found this site – menwholooklikekennyrogers.com - it’s not a bad name, but it’s a must see for a brilliant topic for a site.

today was

Today was a sad day for much of the world with the passing of MJ, The King of Pop, at the age of 50 of a heart attack. Also passing away, but with much less fanfare due to Michael’s passing, was Farrah Fawcett of Charlie’s Angels (the original good one) fame. She too was an icon of the entertainment industry, and will be missed.

Meanwhile, I had another exam, which was notable for it’s content as always. The first question being worth 53 of the 120 marks was awesome in so many ways!! Anyway, it’s done now, and on to the next one. Having said that, this paper has been great because it pushed me into different ways of doing my database set up etc, which has been great as I write the sites. It also made me get in-depth on my xml and styling of xml, so all-in-all not a bad thing.

I have one more exam to go, on Monday afternoon, then it’s off for a week of relaxation and no real scripting or coding. Dependent upon internet access I’ll be blogging still with photos, so I should get back into at least daily updates. Check my twitter though, because I’ll be updating that more regularly from my phone and so on. On that note, please come follow me on my twitter so I can feel the love. I broke 30 followers the other day!!! So I’m not in Ashton’s league yet. Packing for the Brisbane trip will be Monday evening and through the night, before leaving the house at 3 or 4 in the morning.

I’ve planned my flight already, as I am intending to have backed up all my data before flight so I can wipe my laptop and do a whole new clean start. I’ve got my Windows 7 ready (or maybe an XP Pro install), as well as all my Office Pro and other software ready. I have to still use MS Windows because ONE of my papers requires assignment submissions in MS Project. No Ubuntu for me then, except the rebuild of the server box once I get back from Aussie. I’m also hoping to get a bit of work done on my tutorial type pages, so I can add them to here once I get back from Aussie. All these promises I keep making, but it is true: the next few weeks will have the site launch (hopefully the 2nd not too far after) and also have the tutorials hit the web too.

I’m also gonna be having an interesting conversation with the IRD tomorrow, as they just declined my claim for donation rebates as

[t]ax credits can only be allowed for amounts paid or donated that are equal to or less than your total income. [Their] records show that [my] income from salary/wages or benefits for the year was $0.00.

That’s cool, and I’m not going to dispute that, as it is the most recent communication from them and so the most up to date. The interesting part will come when I ask them (on the basis of a letter received two weeks earlier) to refund me the money they acknowledge I paid as PAYE (Pay As You Earn income tax) as since I earned nothing (according to them), I really should have no tax to pay. I’m sure they will kick up a stink at me, and find new records; but all I can do is look at the veracity of what they officially document to me, and as they have officially told me – I earned nothing.

I’ll keep you updated on this, because I can see it turning into a bit of jargoned legalese rubbish where they find a way to say I earned nothing but owe them thousands in taxes….

Update: 27.06.09 - I did call them today. The result being that they decided that they had made an error in their most recent letter, and they now acepted that I had earnt money last year. Funny that, I told them I was happy to accept their most recent decision, however they preferred to refund me a couple of hundred dollars, rather than a few thousand dollars. Apparently it will now take at least 6 weeks to process?? Hmmm, I said, you’ve just done the whole process on the phone for me right now; what more is there to do except start the direct credit to my account? Ah, well, that’s the government for you; always taking extra long to give you money back without interest, but penalising you immediately if they think you might owe them money.

Here’s a classic bit of internet lol’age, the baby panda video. You’ve either seen it ages ago (so you need to see it again) or you haven’t seen it before (so you really do need to see it), so here it is:

That’s it for tonight, and I’ll be back with more tomorrow.

today is a day is a day

Well, the site is definitely done now (except the final cronjob stuff – yep, it’s still hanging around!!) and I worked some hacks and fixes for dealing with Internet Explorer….ran trials in my 9 or 10 browsers, and the only fails were IE, Maxthon, and other IE-based browswers. Solved that by scripting two sets of css and JavaScript dependant on what the browser in use is. Why can’t Microsoft just build a real standards-compliant browser and be done with it?? I’m probably going to delay actually going live for another few weeks as I have two more exams to prioritise, then I fly out the following morning for a break, so realistically won’t get to confirm my testing on the live site ’til after I get back.

This morning I had the privilege of watching a pod of orcas swimming up along the coastline of the Waitemata Harbour, and back down again. They’d been inshore over-night, and then started moving around again. When I first saw them, they were surfacing quite a bit and display a lot of body and dorsal fin, as well as some white patches. By the time I raced home and got my camera, they’d calmed down a bit and were only surfacing enough to show a small amount of dorsal. Beautiful creatures, but unfortunately my pictures don’t show this to great effect. I’ll post one pic, just so you can see a bit, and also the beauty of the islands out in the harbour.

Otherwise, just been studying hard (trying to!) and getting ready for exams. Haven’t done much prep or planning for trip yet, but shouldn’t need much. I’ll be packing on Monday night after the exam, because I figure I don’t need sleep and can do that on plane. I’ll also be re-loading my laptop that night. I’ve got to install Office Pro once I can take my time…I still have to use MS because of specified programs for my Information Systems papers (MS Project is a required submission standard), but my other box is about to be re-built as a Linux and LAMP box. Again, just haven’t had the time to do it so far this year, and allowing time to remedy issues. It will happen, just all in good time.

In other news, what I predicted all along has come to be true for thestory about the Belgian girl who got 56 stars tattooed on her face. She has now admitted to lying because her father was angry (link here @ dailymail.co.uk). I knew no tattoist would “accidentally” or deliberately do 56 stars when only asked for 3. At least he has now been vindicated after a week of crap from everybody.

Best go back to the study, so I’ll just leave you with this great version of Katy Perry’s “Hot and Cold”:

Enjoy.