Poll

Aboot this blog

This blog is a cross between a scrapbook and a diary. I hope you find something here of interest. If you’d like to keep up with things as I see them then you can subscribe to my news feed by clicking the icon below:

feed-icon-28x28.png

DISCLAIMER

Please note that any reviews/recommendations are based solely on my own experience and does not constitute a guarantee that you will have the same experience. Please do your own research before parting with any money - the risk is all yours!

The Briticiser

Looking for my world famous US to GB English spelling converter?

THE “BRITICIZSER”

www.us2uk.eu

IT’S HERE

Tags

  3D Stuff     Plausible Lies     Overcoming Disabilities     Education     Windows     Software     PHP     Excel     Equipment     Acrobat     Odds and Sods     Family     Flatpress     Reviews     Utilities     Tutorials     Word     Bits and Bobs     VBA         News     Bookmarks     Hardware     Browsers  

Find me on Facebook

facebook-icon-28x28.pngVisit my Briticiser Facebook Page to keep up with things here - lots of reviews of stuff I’ve really used and also all kinds of helpful computer tips with a heavy bias towards SAVING YOU TIME.

Adding hours up in Excel

Permalink: http://paul.us2uk.eu/?x=entry:entry170904-120009

One of the common uses for Excel is time recording but it can get a little confusing when you try to add hours up to get a grand total because, in normal practice, Excel will apply the same cell formatting to the cell with your total in as was applied to the times shown in the individual rows.

Why this is a problem is demonstrated in the following example - while you can probably get the right answer you’re looking for on each individual row, this is because the number of hours is less than 24 but, when you start adding these up (and go over 24 hours) Excel will start counting each 24 hour block of hours as 1 day, and your cells may not be formatted to display those days.

This is what happened below and you can see the custom formats I used to display exactly the same total in different ways. Remember that you can quickly access the custom formatting options by selecting the range of cells you want to format and then pressing Ctrl+1 on your keyboard.

image

Just remember to use an “h” in square brackets like this if you want Excel to treat the cell as the total number of hours (and not days and hours). If the format you’re looking for isn’t displayed in the list of formats then simply type it in yourself in the “Type:” box, and click “OK”.

image

If what you’re looking for is decimal hours (i.e. 72 point five hours instead of 72 hours and 30 minutes) then you need to change the format to a simple number format (e.g.

image

How to crack the password on an Excel VBA Project

Permalink: http://paul.us2uk.eu/?x=entry:entry170809-124855

I never set passwords on my VBA Projects. Initially this was out of pure laziness but they’re not secure anyway so setting one is pointless if the sole reason for setting one is to keep your code a jealously guarded secret.

Now, there are legitimate reasons why you might want to crack a VBA Project password and I recently needed to do just that on one of the workbooks in our office which was causing the user a problem when trying to copy and paste from one sheet to another: when the user selected a different tab they also “lost” whatever was on their clipboard so they couldn’t do the usual thing of selecting a cell in the sheet where they wanted to paste something when the “Select destination and press ENTER or choose paste” message appeared at the bottom, because this message disappeared when they selected a new sheet.

There was obviously some script being triggered when they selected this sheet and, if you carry out any operation on a sheet before you get chance to paste your information in, then that chance goes begging.

Unfortunately this particular workbook was one they’d inherited from a user who had long-since left the company and the VBA Project had been locked with a password to protect the code… but no-one knew the password.

Of course there are solutions out there which you can download or buy but, if you’re already comfortable with VBA (otherwise why would you want to get in there to fix things?) then here’s an easy solution which works for all versions of Excel, both 32 bit and 64 bit.

https://stackoverflow.com/a/27508116

Make sure you click the link (or scroll down the page) to see the corresponding code for 64 bit versions of Excel (you need to use the code which applies to your version).

Credit for this solution goes to Duc Thanh Nguyen.

The basic instructions are that you copy the code given into a new workbook, save that with whatever file name you want, then leave the workbook open.

Switch back to your workbook with VBA password protection enabled and click on the Developer tab, then Macros, and choose to run the macro called, simply, “unprotected” which should pop up a nice friendly message like this:

VBA Project is unprotected message box

How to create an alphabetical list in Excel

Permalink: http://paul.us2uk.eu/?x=entry:entry170331-151659

There didn’t seem to be a way to create a custom list in Excel which would allow you to put letters of the alphabet down one column (unless you typed one in by hand for however many letters you want to go down to) so here’s a formula I dreamt up to do just that.

=SUBSTITUTE(ADDRESS(ROW(),COLUMN()+ROW()-COLUMN(),4),ROW(),”")

This will work in any column and will keep incrementing the lettering well past “Z” (which is a limitation of some other formulae I found to do the same job).

My formula will only work if you start the sequence in the first row because it works by setting the column number as the current row number - so row 1 will be “A”, row 26 will be “Z”, row 5000 will be “GJH”, and so on.

If you need to start your list in any row other than row 1 then you’ll either need to adapt the formula somehow (good luck with that - but please let me know if you have any success) or simply create a list starting in row 1 on a new blank worksheet then the list and paste it as values (so you just get A, B, C, etc. instead of the formula) in the cell where you want your list to start. The keyboard shortcut I use to paste values is Alt-E,S,V., or you can do it the standard way - see the help section for your version of Excel for instructions if you get stuck.

The beauty of this is that the exact same fomula work in any cell and in any column.

image

Formulae for Roots & Powers in Excel

Permalink: http://paul.us2uk.eu/?x=entry:entry170304-003048

If you’ve ever wondered what the little “hat” symbol (^) is for, generally above the number 6 on your keyboard, then your further enlightenment is at hand.

It has a number of uses, for instance you can use ^p in the search & replace function in MS Word to find and replace paragraph markers - useful when you need to remove extraneous line breaks when you paste a block of text in which doesn’t format correctly.

However the ^ symbol in Excel is used to denote powers so, if you want to find the square or cube of a number, rather than use the more cumbersome POWER worksheet function you can just use n^2 or n^3 to find the square or cube of a number.

Here’s how to use that in a formula. [Read More…]

Excel Functions - Version Compatibility

Permalink: http://paul.us2uk.eu/?x=entry:entry161117-162809

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.

http://www.professionalexcel.com/2011/06/replace-iferror-with-ifiserror/

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..

https://docs.google.com/spreadsheets/d/1ztUAhgAghKn2wNih7-QgMbSAKfux4w_qkAcJdD82bLU/edit

Here’s the same thing downloaded for posterity as an XLSX file

Using the current row or column number in a lookup formula

Permalink: http://paul.us2uk.eu/?x=entry:entry161017-131301

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…]

Merging cells in Excel can lead to formula errors

Permalink: http://paul.us2uk.eu/?x=entry:entry160930-180600

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…]

Column A missing in Excel?

Permalink: http://paul.us2uk.eu/?x=entry:entry160922-232228

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.

image

image

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 Excel Hidden Column Widener Cursorwith 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.

How to return the text before or after the first or last space in an Excel cell value

Permalink: http://paul.us2uk.eu/?x=entry:entry160830-163100

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…]

Find and Replace Line Breaks in Excel

Permalink: http://paul.us2uk.eu/?x=entry:entry160812-105344

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:

ABC
XYZ

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…

http://blog.contextures.com/archives/2013/05/28/find-and-replace-line-breaks-in-excel/

Windows shortcut files are a little more useful than I thought

Permalink: http://paul.us2uk.eu/?x=entry:entry151214-092353

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…]

Can’t delete or rename a folder in Windows 7? Try this

Permalink: http://paul.us2uk.eu/?x=entry:entry150907-162937

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.

folder-error-msg

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…]

Excel 2010 Solutions to the Clipboard Cannot Be Emptied Problem

Permalink: http://paul.us2uk.eu/?x=entry:entry150514-124113

In Excel 2010 you may run into an annoying problem trying to copy things to the clipboard:

clipboard-cannot-be-emptied

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:

http://www.mrexcel.com/forum/excel-questions/506582-excel-2010-pop-up-message-clipboard-cannot-emptied.html

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)

How to have mixed font colours in Excel comments

Permalink: http://paul.us2uk.eu/?x=entry:entry150513-174658

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.

excel-comment-text-colours 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…]

The save failed due to out of memory or disk space

Permalink: http://paul.us2uk.eu/?x=entry:entry150507-125645

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…]

How to restore file tooltip popups in Windows 7

Permalink: http://paul.us2uk.eu/?x=entry:entry150320-164842

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.

InfoTip balloon in Windows XP 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:

InfoTip balloon in Windows 7 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:

;System.Title;System.Subject;System.Comment

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!

Folder comments in Windows XP

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:

Folder comments in Windows 7In 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.

 

Happy computing!

Task Time Counter

Permalink: http://paul.us2uk.eu/?x=entry:entry141205-170816

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.

image[Read More…]

Excel tip - Converting Unix Timestamps

Permalink: http://paul.us2uk.eu/?x=entry:entry140512-154624

imageYou 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…]

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.

Support our caregiving

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.