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:
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. 🙂
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):
Font: Bold, White
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:
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:
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:
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...
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".
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.
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.
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.
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 22nd 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
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 2nd 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.