Excel shortcut for copying formulas

I found another quick Excel shortcut the other day.  (Courtesy of LifeHacker) When you want to copy a formula down a column, I typically grab the bottom right corner of a cell, and drag the mouse down through all the cells I want to copy the formula too.  This is pretty easy and fast, which is probably why I never found this new shortcut before.  An even quicker way is to simply double click on the bottom right corner of a cell, and it will automagically fill all the cells downward in the column until the last one with data in the column to the left.  Example:

Screen Shot 2013-06-28 at 6.45.04 AM

Here I have a column on the left with data, and the column on the right with a simple formula that I want to apply to that data.  Typically I would just grab the bottom right corner and drag down to copy the cell.

IMG_20130628_064555

An even faster way is to double click when the cursor changes to the black cross, and it will automagically copy the formula for you.

Screen Shot 2013-06-28 at 7.22.28 AM

Hope you found this useful and time saving!

Internet connection in VirtualBox guest

Another pain point that I found a quick solution for.  I thoroughly enjoy my specced out MacBook Pro, but sometimes I still must run Windows for things like 3D CAD with SolidWorks.  For this I use a free virtual machine tool called VirtualBox and run Windows 7 seamlessly and flawlessly.

However, occasionally I’ve had trouble connecting to the internet in Windows (the guest).  I searched around for almost an hour, and found a quick solution that takes less that 5 seconds. (seems to be a trend, no?)

While running your guest OS, in my case Windows, go to the VirtualBox menu at the bottom and click on “Devices -> Network Adapters”.

Screen Shot 2013-06-11 at 6.48.23 AM

Choose “Attached to: -> NAT”

aa

That’s it!  Even though the little internet icon in the bottom right still shows some kind of error, Screen Shot 2013-06-11 at 6.54.07 AM the connection worked fine for me.  Hope it works for you!

Dimension the distance between arcs in SolidWorks

I was having trouble doing something very simple today, and learned an easy fix for it. In SolidWorks when making a drawing of your 3D CAD model, the default ‘Smart Dimension’ gives the dimension between the centers of two arcs, which is typically what you want.  But what if you want to dimension between the edges of the two arcs themselves?

In this drawing, if I choose ‘Smart Dimension’, it will grab the centers of the two arcs and give me the .75 dimension.

Image

To get the actual edges, first create the dimension the same way as above, letting the ‘Smart Dimension’ get the centers for you.

Then click on the dimension again to bring up the “Dimension Property Manager” menu on the left.  Click on the “Leaders” tab, and scroll to the bottom. Under the “Arc Condition” header, choose “Max” and “Max”.

Image

Now it will give you the distance between the edges of the two arcs.

Image

Simple, but hard to find if you don’t know where to look.  Hope you found it useful!

Credit for this info comes from this article in the SolidWorks help documentation.

Reduce the size of Time Machine Backups

I found this great blog post by Hawk iMedia that details a very simple utility to compress or compact your time machine backups.  If you’re like me, Time Machine is way overkill.  I just want one viable backup that is fairly recent.  Not daily/hourly backups until my entire 3TB backup hard drive is full, as Time Machine will do by default.  So I roll with automatic backups turned off, and just manually backup occasionally.

Anyway, the Time Machine backup file (.sparsebundle) was taking up 1TB on my Time Capsule drive, even though my Macbook Pro only has a 500GB SSD hard drive.  So based on the aforementioned blog post, here is how to reduce the size of your Time Machine backup by compacting or compressing the .sparsebundle file.

Its basically just calling a “hdiutil compact” command on the sparsebundle file.

First, open Finder (or my favorite replacement, TotalFinder) and navigate to the folder where you’re sparsebundle file is located. Or in Terminal you can use the “ls” and “cd” commands to navigate through the folder structure. The “cd ..” command moves up one folder.  Here is what my Terminal commands look like:

Image

If like me one of the directories or folders has spaces or special characters in the name, this nifty trick will help you navigate to that folder.  Now you also need to be root to run the “hdiutil compact” command, so sudo into root.  You will need to enter your admin password.  You should be aware that if you run the wrong commands in root, it is possible to mess up your system.  However, just type in the commands directly and you’ll be fine.

Image

Image

And, you’re done!  So easy!  Now, I know that it shows “0 bytes reclaimed” on my system.  But after I ran this command on both mine and my wife’s sparsebundle backup files and got that message, I checked my Time Capsule and found that it was reclaimed 800GB of free space!!  So it definitely works.  Try it out!

Terminal Directories with Spaces or Special Characters

I’ve been trying to reduce the size of the Time Machine backup on my Time Capsule with a nifty trick that I’ll share in the next post.  In the process I learned something very useful in dealing with Terminal commands, in particular with directories.

The name of the directory I was trying to ‘cd’ into had spaces as seen below.  I used the “ls” command to show a list of drives in /Volumes.

Image

If I simply type “cd /Andrew Cockerham’s Time Cap” it will give an error “no such file or directory”.  So to get into that directory, the easiest way is to wrap it in quotes:

Image

You can ignore the fact that I’m in ‘root’ to do this method, you do not need to be in root for it to work.

The second way to do it is using backwards slashes, ‘\’, before each space in the directory name:

Image

Note also that back slashes must be in front of the special characters, in this case the apostrophe.

Now this leads me to the next trick.  Typing out this long directory name with all the back slashes can be tedious, so a great shortcut is to simple start typing the first few letters of the directory, and then pressing the “Tab” key, and it will autocomplete the directory name for you, automatically adding the back slashes where need.

Image

and autocomplete…

Image

I love neat little keyboard shortcuts, especially in Terminal.

Hope you found it useful!

Free From iTunes – RSS feed podcasts

I’m a big fan of the Freakonomics podcasts, and the books/blog as well.  I can barely wait a whole week in between episodes, and I’m always checking my TuneIn Radio app to see if there is an updated episode.  Which, by the way, I wish TuneIn would let you subscribe for notifications when new episodes of my podcasts are posted.  And why can’t I ‘favorite’ the Freakonomics podcast?

Side note, I just read that the TuneIn Radio app and others like Pandora can sync with your car using MyFordSync.  Unfortunately my wife’s 2012 Ford Focus isn’t yet supported, even though the 2013 model is.

Anyway, I wanted to listen to another one of my favorite podcasts on my phone, the Breakaway podcast. But since I have an Android phone, I of course don’t have iTunes on my phone.  And I wouldn’t want to anyway, as iTunes is far and Apple the most terrible product Apple has ever created (but we’ll leave that for another post).  So I was trying to figure out how to listen to podcasts on my phone, and more critically, how to get the RSS link for the Breakaway podcast on iTunes.  I use Podcast Addict. But to get the RSS url, that was a neat little trick.

Adam Schlitt made a sweet free website that converts the iTunes link into an RSS feed link for you.  Then the RSS link can be used to subscribe to the podcast in whatever non-iTunes software of your choice.

Hope it was useful to you, and happy podcasting!Image