Is it just me, or if the Find and Replace tool in Excel (actually in Office) really limited?

The first deficiency is that I want to be able to select “Within Selection”, not within “Sheet”. I know that it defaults to the selection if you try this on a multi-cell selection, but if you want to replace references in a single formula in a single cell… watch out! I usually copy my formula to notepad and do the work there.

Now, the options above are fine for really simple find/replace scenarios, and have served us well for years, I suppose. Yet it still leaves me wanting, especially right now. Consider this formula:

=ROUND($D$69*(SUM($AG66,$E66:AQ66)+SUM($AG93,$E93:AQ93)+SUM($AG120,$E120:AQ120)+SUM($AG157,$E157:AQ157)-SUM($AG163,$E163:AQ163))/SUM($AV$66,$AV$93,$AV$120,$AV$157,-$AV$163)-SUM($AG69,$E69:AP69),0)

It’s pretty hideous, but I need to modify it to read as follows:

=ROUND($D$69*(SUM($AP66:AQ66)+SUM($AP93:AQ93)+SUM($AP120:AQ120)+SUM($AP157:AQ157)-SUM($AP163:AQ163))/SUM($AV$66,$AV$93,$AV$120,$AV$157,-$AV$163)-SUM($AP69:AP69),0)

So pretty much, what I need to do is

• Replace all the $AG references with $AP
• Get rid of the ,$E##? pieces

The existing tools works fine for the first part, but can’t do the second since the ## pieces change.

Wouldn’t it be nice if Excel had pattern matching? I’d love to be able to knock up a search to check for a pattern like comma dollar E number number [number] colon and replace it with a colon.

Even though this might seem a little strange to have a variable pattern here, I’m pretty sure that you can do exactly this with Regular Expressions, and quite quickly too.

What do you think? Something you could see value in?

As I write this, it’s currently 29 degrees Celsius outside which, according to the Weather site, feels like 35. And it’s 10:00PM. Meanwhile, inside my house it’s 31 degrees… does that mean it feels like it’s 37 in here? It’s hot… yuck.

And the weather forecast for the next few days gets even better:

I guess I shouldn’t complain… I just checked today’s actual results, and our high was 35 degrees which felt like 40. So this forecast actually means it’s cooling down… wow.

At any rate, that’s it for me for tonight. My laptop is oversweating, and so am I.

If you filter data out of large data sets, you may have run into an issue where you can hit 8192 non-contiguous ranges, which Excel couldn’t handle. While this issue has been in Excel for many versions, I can honestly say that I haven’t run into it in years, as I’ve always sorted my data first, to avoid this issue.

To replicate the issue, try this in any version of Excel prior to 2010:

Enter the following data in a blank sheet:

• A1:    Row
• B1:    Index
• A2:    =ROW(B2)
• B2:    =IF(A1=1,2,1)

Now copy row 2 down about 20,000 rows…

Next, select row 1 and Filter your data on Column B to only show 1′s

Now, select from cell A3 to the end of your data in column B and try to copy it… You’ll get a message like this one…

Well guess what… that’s no longer necessary! In Excel 2010, the Excel team has removed this limit. And while I haven’t tested this, the new limit is apparently imposed by the memory in your computer, not a hard coded limit.

It’s great to see that such a long standing bug has finally been resolved.

Cherisse asks:

Is there a way to set a conditional format for a cell or row to look at the date and format if that date falls on a specific day of the week. I.e. a date input as 7/21/09, cell formatted to show “Tuesday, July 21, 2009″, want to format all cells that contain the word “Tuesday” as red.

I know from experience that many users seem unaware that dates in Excel are actually numbers. It drives me crazy, as it can easily bugger up formulas later on down the line, but fortunately we can deal with this.

So Cherisse, the answer is… Of course! One thing I would advise though… if you can, prevent the user from entering an invalid date to begin with using Data Validation first, then you don’t have to worry about the garbage ever getting in to your spreadsheet. But, because you asked, I’ll walk through the conditional format route first…

Re-Active Date Validation Using Conditional Formats

To validate this data, I would go through two steps.

The first step is to set up the format on the cell for the correct input type. So right click the cell, choose “Format Cells”. On the Number Format tab, choose Date, and choose the date format:

Second, we set up a conditional format. In Excel 2003 go to Format –> Conditional Formatting  –>  Formula Is. In Excel 2007 go to Home –> Conditional Formatting –> New Rule –> Use a Formula…

Set up the rule as follows (assuming the input cell is in B5):

• Formula:     =IsNumber(B5)=False
• Font:          Bold, White
• Fill:            Red

This will highlight the cell in red if the input is not numeric, as shown below. (I copied the format to cell B6 as well.)

Okay, so this is great, but what if we want to change this so that anything that isn’t a valid Tuesday is highlighted? We do this by modifying the conditional format’s formula:

• Excel 2003    =OR(ISNUMBER(B5)=FALSE,IF(ISERR(WEEKDAY(B5)<>3),TRUE,WEEKDAY(B5)<>3))
• Excel 2007+  =OR(ISNUMBER(B5)=FALSE,IFERROR(WEEKDAY(B5)<>3,TRUE))

Pro-Active Date Validation Using Data Validation

As I mentioned above, I’d actually approach this a little differently, and prevent a user from even entering invalid data in the first place. The method to do that is Excel’s Data Validation tool.

In Excel 2003 go to Data –> Validation. In Excel 2007 go to Data –> Data Validation

I usually set up the rule as follows:

• Allow:           Date
• Data:            Greater Than
• Start Date:    =Today()-1

This rule means that you can enter today’s date or higher.

We want only want the user to enter a valid Tuesday though, so let’s change this a bit and go with the following rule:

• Allow:         Custom
• Formula:    =WEEKDAY(B5)=3

The real slick thing here is that you don’t need a conditional format, as the user will be prevented from ever entering anything that is not a valid date AND a Tuesday.

One word of advice though… the default error message isn’t really intuitive:

You can customize this error by filling in the Error Alert tab. I usually put a nice descriptive error in there to tell the user what to do. Something like:

This field only accepts Tuesday’s that are entered in a valid format. Please enter a Tuesday in the format MM/DD/YY.

The effect will look like this:

And if you want to be even more helpful, fill in the Input Message tab as well…

Conclusion

Wherever possible, you should try pro-actively validate your data before the user can even commit it. The Data Validation tool is great for this purpose. You will find, however, that there will be times where you just can’t pro-actively evaluate everything… and that is where conditional formatting can be really handy as a Re-Active Data Validation tool. I often use the two in combination to generate a really robust interface.

This morning I attempted to boot my laptop, only to get a really nice error message:

“User profile service failed the logon. User profile cannot be loaded”.

Nice.

So after trying a couple of reboots to no effect, I went and Binged (yeah, that’s right, I use Bing now) the error message. The first hit was this awesome forum post by an MVP.

I was bank up and running in just a few minutes.

It’s always nice to find good quality, step by step instructions out there to help with things like this. I still don’t know what caused it, nor do I really care, but at least I’ve got all my settings back.

As the IT guy at work, I’m responsible for making sure that we’ve got good backups available in case anything goes wrong. Like many corporations we use tape backup which we rotate off site. It works reasonably well as we’ve got it down to a routine, with daily and weekly backup tapes, ensuring that we can roll back 1-5 days or 1-5 weeks. (Or less, as we also use ShadowCopy on our servers.)

At home though, it’s a different story. I find its way harder to get into a backup schedule at home, for a variety of reasons. Despite making sure we have backups at work, I’ve been notoriously bad about backing up the data on my personal machines.

This obviously leads to the inevitable “What If” questions… what if the house burns down? What if a computer was stolen? What happens if I lost my laptop? What happens if we have a catastrophic hard drive failure? What if… So last week I decided to actually deal with this issue.

Acronis True Image

I installed a copy of Acronis True Image (an older version) that Acronis was generous enough to provide to me a year or so ago. I managed to backup my laptop to an external USB hard drive, which was great. But there’s this thing about backups… as my friend John once said “The value is not in the backup, it’s in the restore.” To complete a backup test, I decided to restore it to another machine that I have in the office.

It was a no-go. Not the fault of Acronis, to be sure. I’ve heard great things about their products, and I’m sure it would have worked just fine. The issue is that I backed up 200GB of stuff, and the machine I could mess around with only has a 75GB drive. It just won’t fit.

So I’ve still got that backup, but it’s not really as portable as I want. And while the new version of Acronis has a “set it and forget it” feature, this one doesn’t. So that still leaves me needing to manually trigger backups which, as I mentioned earlier, I’m not very good at remembering to do. So I gave up on Acronis, looking for a better solution.

One thing I would say about Acronis is that I’m not really a fan of the restore interface. It strikes me much like the old Norton Ghost versions which aren’t quite clearly explained. I don’t use this product much, so what I’d like to see are screens that read something like the following:

• What file would you like to restore FROM?
  • Show the list of drives, but mark which are internal and which are external drives
• Where would you like to restore TO?
  • Show the list of drives, again mark which are internal vs external
  • Clearly explain that the drive you select here will be overwritten

I just really didn’t find it was worded quite like that, which made me a little nervous.

Mozy

So at any rate, I kept looking, and re-visited Mozy, eventually holding my breath and shelling out $9.90 for a month of backup to test it out for our PC’s.

Mozy seems pretty slick. It basically works like this:

• Flag the files you want to back up
• It compresses and encrypts them
• It uploads them to Mozy’s server
• They are available to restore

The actual backup interface is pretty simple, and you can pick the speed it uploads at if you want to scale up/down your upload speeds. The shot below is a backup in progress. Keep in mind that I don’t have much bandwidth here at home, since I’m running over a wireless G connection.

There is something misleading about the image above though… You’d think that pressing “Cancel Backup” would actually cancel the backup. It doesn’t. It stops it temporarily, as you can see below:

I also tested the restore. Again, the interface is pretty simple, as you can see below:

It took about two minutes to “find the file on the server”, and then the restore of the file was pretty much instant. The file works just fine.

Some of the things that really sell me on Mozy are:

• It has “set it and forget it” scheduling. Set the backup files and it just works away in the background
• It is on a remote server. So I don’t have to remember to burn discs or write to tape and take it away
• It only backs up the changes going forward, so it won’t consume all my bandwidth while I’m trying to use it

One thing to be aware of though, is that you do need bandwidth to upload your data to their servers. Dee’s backup is over 12GB, and has been working for about 2 days now to send it all along at about 800kbps. That may seem like a lot of time, but the peace of mind of having the family photos backed up is worth it.

My Verdict

I’m pretty frugal when it comes to my software, but so far, I’d recommend this one. I think it has some serious advantages over traditional backup methods, gives good peace of mind and, at $4.95 per month for unlimited backup for home users, it’s pretty affordable.

If you’re interested, you can find out more at www.mozy.com/home.  I was also made aware by email of a full review at onlinebackupsreview.com, where you can also save 20% with a Mozy Promotional Code.

Well, here I go again…

After being up at a full contingent of staff for a while, I have one of my team leaving me to return to school. It’s unfortunate for me, but in her best interests, so we’re wishing her well.

This leaves us with a job opening for a Senior Accountant again. So if you are interested, or know someone who is, please point them here. For reference, I’m hoping to make a hire on this position before the end of July, so I’m going to be reviewing resumes up to July 22^nd only.

Here’s the full text of the job posting:

Senior Accountant – Fairwinds Community & Resort

The Fairwinds Community & Resort is central Vancouver Island’s premier master-planned, oceanfront community with over 700 homes and homesites. It includes the award-winning Les Furber designed 18-hole Fairwinds Golf Club, the 20,000 square foot Fairwinds Centre health club and members’ facility and the 360 berth Schooner Cove Marina. For more information, visit www.fairwinds.ca.

Fairwinds is currently seeking an enthusiastic individual to fill the role of Senior Accountant. This is an exciting opportunity to grow your skills in a growing company whose activities span real estate development, food & beverage, golfing, and marine operations.

Your accounting functions will include:

• Creation of working papers and journal entries
• Review of working papers to support trial balances
• Generation of financial statements for review with the Controller
• Coordinating regular inventory counts & investigating variances
• Assisting with budgets
• Reviewing cheques for adequate support
• Review and documentation of systems internal controls

As the successful candidate, you will:

• Pay attention to detail
• Be able to recognize and solve problems of a technical nature
• Be enrolled as a student member in the 3^rd or 4^th level of an accredited accounting organization, (CMA, CGA, CA.)
• Possess in depth knowledge of Microsoft Word & Excel

Your accounting experience in one or more of our core industries will set you apart from other candidates.

Fairwinds also offers an excellent benefits package including MSP, extended health, dental, facility usage and an optional RRSP & pension plan.

Please send your resume and salary expectations in confidence to kpuls@fairwinds.ca. No phone calls or drop-ins please.

Only those selected for an interview will be notified.

I found this kind of interesting…

This is an excerpt from a BestBuy job posting… specifically for the Sr. Manager for Emerging Media Marketing:

• Basic Qualifications:
  
  • Bachelors degree Liberal Arts, Marketing, Interactive Marketing, New Media, Business Administration or related
    
  • 2 plus years of mobile or social media marketing experience at an Agency director level, strategist level, or brand interactive director level
    
  • 4 plus years People or resource leadership experience
    
  • 1 plus years of active blogging experience
    
• Preferred Qualifications:
  
  • Graduate degree
    
  • 250 plus followers on Twitter
    

Both blogging experience and twitter followers required…

I suppose with a title like that, it makes sense in a way. To me though, this is weird… I’m not a marketing guy, I’m an accountant.

The full job posting is here. Credit to Steve French for posting this on his Twitter Feed.

As I mentioned in this post, I started working my way through the .NET Hello World example in PED 2nd Edition. I ran into a funny issue though, where it kept opening up my application in Excel 2003 instead of 2007. I fired off a quick email to Dennis who was kind enough to give me some pointers.

As it turns out, my “Current Version” of Excel on my laptop was set to 11 (Excel 2003). My attempts at running Office 2007′s Detect and Repair feature, to reset this to 12, were causing the Microsoft Bootstrapper to crash. (So it wasn’t completing.)

Today I decided to install Acronis TrueImage to take a backup before I mucked with this any further and, lo and behold, it pointed out that it couldn’t install until it killed the suspended install of Office 2007. I gave it the assent to kill the job, and then my Detect and Repair ran fine. I’m now back up to Excel.12 as my current version, and my “Hello World” application works nicely.

I’m only about ½ a chapter into PED 2^nd edition, but the .NET stuff is working for me so far. I’m looking forward to digging a little deeper into this now, and trying my hand at a full managed COM Add-in.

After playing around in Visual Studio, and taking my first foray into the VB.NET world, I thought it might be kind of interesting to try and see just how portable .NET code really is. So I decided to port the little Math Game to my Windows Mobile 6 phone.

Guess what! I did it, and it wasn’t actually all that difficult to do.

The conversion of the code was pretty simple. I just exported the userform from my original Windows project and imported it into a new Windows Mobile 6 application. (Err… after I downloaded the Windows 6 SDK which doesn’t ship with VS2008 Pro.)

It worked just fine in the Emulator, so I then set about the deployment process.

It took way longer to get the deployment done than to write the project, I’ll tell you that right now. You can’t install an exe file on Windows Mobile 6, but rather have to package it up in a cab file. Great, but how do you do that?

After some searching, I finally found http://msdn.microsoft.com/en-us/library/zcebx8f8.aspx, which takes you right through the process. Fortunately I do have a digital signature at work, so I was able to use that to sign the code as well. I’m not sure if the certificate would be trusted on anyone else’s device or not, but it did work on mine.

So once I deployed the cab file to my phone, the install was easy, and the app works. It just goes to show you just how portable simple .NET code can be, which is really cool.

Despite all the goodness here, there are a couple of quirks that I ran into…

• In the emulator, I can get my shortcut to show up in the Games folder off the start menu. For some reason I can’t get that shortcut to show on the actual device though. Instead I have to dig down through the File ExploreràProgram FilesàGames. Oh… wait… I think I got it. I was setting up a shortcut in the Games folder, but it looks like it needs to be in the “Games Files” folder.
• My icon file only shows up when the shortcut is selected. When it’s not selected it looks like a DOS box.
• I can’t figure out how to turn on the NumLock when the game starts. (I’m sure it’s easy when you know what you’re doing.) This means that you have to hold down the FN key in order to get a number into the box.
• On my phone it also enters the numbers in a pop-up box, which you have to confirm, before you can press enter. (Nevermind… that’s the XT9 Predictive Text feature.)
• I wish I could stop the incessant beeping when you click enter and the messagebox pops up, but don’t know how to do that.

At any rate, on the whole it works, and that’s the good thing.

I have no idea if this works on Windows 6.1 or Windows 6.5, but it seems to work on Windows 6.0 for me. I’d be really curious to know if it installs for anyone else. If you’d like to give it a try, you can get it here.