While working on a spreadsheet today I was using the ISERROR function to test if a VLOOKUP formula returned a value and, if it did, return the value otherwise display nothing - instead of the usual #N/A error notification.
The usual syntax for this formula begins “=IF(ISERROR(VLOOKUP(…”.
I happened to notice that Excel 2010 (the version I use at work) offered up “IFERROR” as a possible function as soon as I typed the “IF” and, indeed, this allows me to write slightly shorter formula. However this is not backwards compatible with Excel 2003 which I use elsewhere so I avoided using it.
For anyone faced with a situation where they have a spreadsheet containing lots of instances where the a formula contains “IFERROR” then help is at hand at the following link - untested, but I’m sure it’ll work. I’ve bookmarked it here because it looks like quite a useful site.
There are obviously a few more functions which were added in later versions of Excel which you should avoid using if you don’t want your spreadsheet to fail when someone opens it in Excel 2003 (which I much prefer!). The other one I avoid for this reason is “SUMIFS” (I use “SUMPRODUCT” instead) but here’s a useful list of all Excel functions showing which versions they are compatible with - plus a useful description of each..
Here’s the same thing downloaded for posterity as an XLSX file
I was working on a monster spreadsheet which contained over 2000 rows of data spread across 54 columns when I discovered that the spreadsheet I’d been given had about 1000 items (i.e. 1000 rows) missing. I eventually received a revised spreadsheet with the missing items so I was now faced with the task of transferring all of the data which I’d meticulously entered in the original sheet, into the new one.
While it was quite simple to select all of the rows and columns in the original sheet and give it a name (using “Define Name” on the Formulas Toolbar), then use this in a VLOOKUP formula to fill in the cells on the new sheet which had corresponding cells with data on the old sheet, because I had 54 columns of data this would have meant manually changing the column number each time I used the formula in the next column along.
Here’s how to avoid having to do that. [Read More…]
As I’ve just discovered after so many years of using Excel if you merge a group of cells and then use a lookup (I only ever use VLOOKUP but it will probably apply to HLOOKUP too) to return the value from that merged group of cells, then you run the risk of getting the wrong result.
Here’s why, and why you should NEVER use merged cells in part of your spreadsheet which you might (one day) want to look up date from via a formula…. [Read More…]
It sometimes happens that you open an Excel worksheet to find that one or more columns are missing - in the example below Column A has mysteriously disappeared - so what’s going on?
Well, before doing anything see if you can spot the difference between the following two images… they’re not identical and the difference is crucial to telling you why Column A is missing.
Did you spot the difference? Take a closer look at the horizontal scroll bar ↑
In the first picture it looks as though you can scroll to the left to see Column A - if nothing changes when you try to do this then Column A is simply hidden from view and there are a couple of ways you can unhide it again - either by moving your cursor to the line just to the left of the letter B in the column headers and seeing if it turns into a horizontal double arrow with TWO vertical bars through it, like this, indicating that there is something hidden there. When the cursor changes this way you can simply click on the line and drag it to the right to unhide the column, or you can type A1 in the Name Box (that’s the white box on the left, just above the column headers) and press the Enter key to go to that cell… even if it’s hidden. This is useful to know because you sometimes might want to check the contents of a cell in a hidden column without unhiding it, so this is how to do that. Once you’ve selected that cell you can then go to Format… Column… Unhide to make it appear (Office 2007+ key strokes Alt+O…C…U).
But what if none of the above works?
In fact it’s quite possible to lose Column A without it being hidden in the usual manner at all, so no amount of trying to unhide it will do any good. What then?
The reason why this may happen to you is if your worksheet’s horizontal scroll bar is fully to the left - this indicates that you have FREEZE PANES activated on your worksheet. In this case all you need to do is unfreeze the panes from the Window menu (Office 2007+ key strokes Alt+W…F) and you will be able to scroll to Column A.
If you ever run into a situation where you want to get rid of everything but the text before or after either the first or last space in a cell value then here’s a great formula to do just that.
The formula looks complicated but, as with all complicated Excel formulae, it all makes perfect sense once you break it down. [Read More…]
One of the most frequent things I forget how to do is the replacement of line breaks in Excel cells. You can put a line break in an Excel cell simply by pressing Alt+Enter while you are typing in your data, but sometimes you want to replace some of the text PLUS the line break.
To do this is equally simple (once you know how) just by entering Ctrl+J so say you have a cell with the following data with a line break in it:
and you want to either replace or even remove the top or bottom line you would go to your Find & Replace dialogue box (press Ctrl+H) to bring this up and then enter either of the following in the “Find what:” box…
To remove the top line type “ABC” then press Ctrl+J (it will not actually show anything because the Find what box is only one line high, and you might just be able to make out the top of the insertion point I-bar cursor flashing below the A), then type in whatever you want in the “Replace with:” box - or just leave it blank if you want to delete that top line.
To remove the bottom line first press Ctrl+J THEN type “XYZ”. In this case you might not see anything when you type the “XYZ” except (again) the flashing top of the cursor moving along as you type the letters on what a line which is hidden by the restricted height of the “Find what:” box.
Thanks to Debra Dalgleish for this excellent tip ever at her equally excellent Contextures Blog…
But only a little. I create shortcuts using VBA all the time and - up till now - I thought that the only extra field which was possible to display information about the target was “Description”, so I was surprised to see a shortcut which I created manually (just in Windows Explorer) showing the title field from the target file. [Read More…]
Occasionally you may receive a Folder In Use message such as “the action can’t be completed because the folder or a file in it is open in another program” when you try to rename or delete/move a folder in Windows 7.
There are a number of suggestions out there which might work for you, but try my suggestion first and you may be pleasantly surprised. [Read More…]
In Excel 2010 you may run into an annoying problem trying to copy things to the clipboard:
The solution which worked for me was to open the Windows Snipping tool:
Start > All Programs > Accessories > Snipping Tool
then click the “Options” button and uncheck “Always copy snips to the clipboard”, then close the Snipping Tool again.
Credit for this tip goes to user dawsopd at the MrExcel.com forum - you can also find a few other suggestions there if this doesn’t work for you:
Here are some other things you can try - I must admit that I still got the message occasionally after trying the fix I mentioned above, but the first solution on the following page (simply press the Esc key to deselect any cells which were already selected when you try to copy another cell) seemed to also work for me.
Fix for Cannont Empty the Clipboard in Office (yeah, I know it’s spelt wrong… but that’s how it is)
Cell comments in Excel are a useful way to alert users to information you want them to know about - often to provide them with an explanation about what a particular cell contains, e.g. what it means or how it has been calculated. It’s also sometimes useful to draw the user’s attention to part of your comment by using a different font colour for some of the words or characters.
In Excel 97-2003 this was simply a case of selecting the text you want to change the colour of and then selecting a new colour from the font colour button the toolbar. Unfortunately later versions of Excel have made it a bit more fiddly to achieve the same effect, though it’s still very easy to do this. Here’s how to do it. [Read More…]
For anyone who has a problem trying to save a Word 97-2003 Document (this may apply to other Office file types too) in Office 2010, specifically where you receive a message saying “The save failed due to out of memory or disk space” then there is an easy fix for this. [Read More…]
Having finally been forced to use Windows 7 on my computer at work I was faced with the loss of a couple of handy features which I’d become accustomed to in Windows XP.
One such problem concerned the apparent inability to show more than the barest of information in the little popup you get when you hover your mouse cursor over a file in Windows Explorer in Windows 7.
Actually that’s only partially true - it still works for local files (e.g. files on your own hard drive) but for files viewed on a network drive - whether mapped to drive letters of not - you only see the file type, last modified date, and it’s size, none of which is really worth the effort of showing the InfoTip in the first place (you can turn the feature off in folder options).
However it IS possible to get all of the extra information that might be stored in the File Properties (e.g. the document title, subject, and comment) by making a simple change to the registry. Here’s an example of how it looks in Windows 7 - you can see there how useful it is to show more information than you can easily display in the columns to the right:
Note that you must have administrative rights to the computer you’re working on in order to make this change.
WARNING! It is also assumed that you are familiar with the Windows Registry and are happy about taking the risk that you could seriously screw your day up if you do something wrong in there. If not then stop right here and go fetch someone who is. For this reason the instructions below are written with less detail than I would normally provide.
If you’re not confident about doing this then please stop right here!
Otherwise, here’s how to do it:
1) Click on the windows button (normally at the bottom left of your screen) and then type “regedit” in the “Search programs and files” box, then hit return.
2) RIGHT click once on the regedit.exe file you should see in the box above the search box and choose “Run as administrator”. REMEMBER - don’t try this unless you know you have administrative rights to the computer - if you know you can install new programs, then you’re probably going to be ok - if not, then you’ll probably be wasting your time doing what comes next because Regedit won’t let you save your changes after all your efforts.
3) Go to HKEY_LOCAL_MACHINESOFTWAREClasses* and add the following to the end of the existing values in both InfoTip and QuickTip:
Type (or copy and paste) everything in blue - don’t forget the semi-colon at the beginning of the line as that’s the “follow on” from the entries which you’ll already find there.
The reason you need to change it in both keys is that Windows will prioritise “QuickTip” over “InfoTip” when you hover over a network file. Microsoft did that in case you are viewing files over a slow network connection, so it’s useful to know you can make that little tweak if your computer slows to a crawl trying to display the popup information.
In some cases (for local files) I also needed to change the InfoTip value in HKEY_CLASSES_ROOTSystemFileAssociations - for instance, for my Word docs (my VBA only generates 2003 versions of Excel and Word files) I needed to make the same change to the InfoTip in HKEY_CLASSES_ROOTSystemFileAssociations.doc
4) Close regedit.
It should be enough, at this point, to simply close and re-open Windows Explorer in order to see the effect of your changes, or you might want to log off and back on to Windows just to make sure.
My other problem (I’m sure I’ll find more - I won’t even mention my hatred of ribbon menus) is the sad loss of the Folder Description column I could add which allowed me to add a comment to a folder using a truly excellent program called HobComment. While this may still work for 32-bit versions of Windows it certainly doesn’t in 64-bit editions but, fortunately, all is not lost!
In my case I still have my old Windows XP PC connected to the network so I simply access this via RealVNC and add my comments there. Displaying the folder description in a column in Windows 7 is actually a piece of cake - simply right click on the column headings in Windows Explorer and add “Comments” column :) Curiously that trick DOESN’T work in XP, but who cares? Here’s how I’ve got it looking in Windows 7:
In case you don’t have an XP machine which you can use to add your folder comments then all is not lost because you can run XP on your Windows 7 computer in order to run old programs like HobComment, by using Microsoft’s very own Windows Virtual PC. I haven’t tried this yet, but I’m sure it will work.
If you frequently switch between one job or project and another then this Task Time Counter which runs in Excel might be just what you’re looking for.
It’s very simple to use - just click on the Switch Task button to bring up your list of jobs and select one by clicking on the drop down arrow.
You may find the formula below useful if you are importing dates into Excel from a list where the dates have been stored as a UNIX timestamp. Some of my web application PHP scripts store numbers this way, hence the need to remind myself here of how to convert them to readable dates: [Read More…]