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.