Monthly Archives: May 2012

Use data from one spreadsheet (or workbook) in a different one

There are times when you may use one spreadsheet, or workbook, to pull together data from other spreadsheets.

To link sheets in the same workbook:

Click into the destination cell, type = (an equals sign) then go to the other sheet and click into the cell that contains the information you want (the donor cell), and hit enter.  (It makes sense to give each sheet a name before doing this.)

To link sheets in different workbooks (or Excel files):

Have both workbooks (files) open; click into the destination cell, type = (an equals sign) then go to the other workbook (file) click into the cell that contains the information you want and hit enter.

This is described below using some screenshots to illustrate.

Workbook with named sheets

A sheet for each day of the week

An Excel Workbook (or file) has more than one sheet.  In this picture I have set up sheets to record what I spend on each day of the week.

 

 

 

 

 

 

Week summary sheet

Week summary sheet

My final sheet pulls together the total for each day.

 

 

 

 

Linking to another sheet

Linking to another sheet

To get the total for each day, I have linked back to the sheet for each day.

Cell B9 in this sheet, links to Monday’s sheet by the command =Monday!C14 as you can see from looking in the command line box.  The cell C14 in sheet Monday contains the total for Monday, and now I have pulled that in to my week summary.  To do this, I clicked on cell B9 of Week, typed = then went to the Monday sheet, clicked into cell C14 and hit enter.

 

 

There is also another workbook (file) which pulls the weekly totals into a monthly summary.

Link to a different workbook

Link to a different workbook

 

In this case, the command line contains the full path to the workbook (file) containing the donor cell and the data of interest: =’C:\Users\Mail\Documents\[pastelink.xlsx]Week’!$F$13

 

 

 

Invert a selection in Photoshop

In Photoshop Elements, when you are editing a photo, you use one of the Selection tools to select part of the photo.  For instance, there is an unwanted object in the photo – draw a selection box round that object, and delete.  Every thing inside your selection box is deleted.

If you want to keep the object you have selected, and delete everything else, then once you have drawn your selection box, choose SELECT then INVERSE.  Delete will now remove everything outside your selection box.

 

SUM and COUNT functions in Excel

A couple of very useful functions in Excel, probably best explained by a simple example.

Imagine I have a bad tenant – he is supposed to pay rent every month, but he’s erratic.  I have spreadsheet set up to keep track, and each time he sends me a payment I enter it in to the spreadsheet.

spreadsheet with SUM function

Using SUM

 

I can use the SUM function to add up the total amount he has paid me over 12 months.

 

 

 

 

 

Spreadsheet showing COUNT function

COUNT function

 

And I can use the COUNT function to keep track of how many payments he has made.

So, I can see at a glance that he has made 7 payments, totaling £900.

In this example, both SUM and COUNT are over the range D5:D15. If you don’t want to SUM (or COUNT) a contiguous range, you can specify individual cells, separated by a comma.

 

 

Spreadsheet showing SUM and COUNT

Using SUM for non contiguous cells

 

Music on my Kindle

I’ve put some music on my Kindle – plug your kindle in to your PC with the USB cable, and drag some MP3s into the music folder.  To listen while you read, hold down the ALT key and press the spacebar (same to stop) ALT and F skips to the next track.

My kindle is the one with the keyboard – don’t know if the other ones do this!

Use Outlook for your work and home e-mails, and keep them separate

If you like using Outlook for your e-mails, and you want to keep your personal e-mail separate from your work e-mail, on the same PC, you can do this by setting up another Outlook profile.

Go to the Control Panel ( from the Start button).
There will be a topic for “User Accounts” – select this.
Now choose “Mail”.
Under “Profiles” do “Show Profiles”.  A new window opens, choose “Add” and follow the instructions, which should take you through setting up a new profile and adding the e-mail accounts that you want to use with this new profile.

Check the option “Prompt for a profile to be used”, and then each time you open Outlook you choose which profile to use.

Put sender’s address on right

Open Word, and look to see if you have a ruler across the top of the page. (If you don’t, find the “View” tab on the top ribbon, and choose “Ruler”.)

Write your address, and the first line of your letter, eg:

The Mill
Mill Lane
Someplace
England

Dear

Now, select/highlight just your address. Looking closely at the ruler, you should see a shape a bit like an hourglass on top of a rectangle.

With the mouse, click on the bottom rectangle of the shape, the Left Indent, and drag it across the page to where you would like your address to be, then let go.
Now, you can click on the first line of your letter, and you’re off……….

Organise photos with two windows open

There are lots of different ways to organise your photos; I put mine into folders and sub-folders – I have a folder for 2011, and then within that I have sub-folders with names like “Summer Holiday” or “Wales Visit”. When you upload your photos from your camera, you can specify which folder to put them in at the time; if you already have photos in “My Pictures” and want to put them into folders, here is one way to do it.

Open “My Pictures”. To create a folder for 2011 photos: Click on the New Folder tab – a new folder is created and you need to type in the name, so type 2011 then hit Enter.
Open the folder you just created.
Create a sub folder called “Holiday”. Click on the New Folder tab again, type Holiday, then hit enter. Open this new folder.
You have opened the folder “Holiday” in the current open window. If you look at this open window, it will say “Holiday” somewhere at the top – this tells you where you are – when you are in this window, you are in the folder you created, called “Holiday”.
We’re going to set up two windows side by side, so you want this open window to only use part of the screen. (The post “Open two windows” is similar to this.)
Look at the top right of the window; you will see three symbols, an X (which closes the window) and two others to the left of the X. One will minimise the window, the middle one will maximise the window, or, if it is already maximised, will make it a bit smaller.
Make sure the window is NOT maximised – so it’s not taking up the whole screen. Move the cursor over the right hand edge of the window – the cursor will change to a double headed arrow – when you get this, hold down the left button of the mouse and drag the edge of the window to the left, making the window narrower. Now, hover the cursor over the strip at the top of the window; hold down the left button of the mouse – you can move the window around on the screen.

Now to open the second window. Open “My Pictures” again as if you were starting from the beginning again (NOT by opening it from “Holiday”). You need to click on “My Computer” or “My Documents”, whichever you have on the desktop, and start from there again.

Once you have navigated to “My Pictures”, make sure it isn’t taking up the whole screen, as above. As above, make this window narrower, then move it over to the left of your screen. You should be able to play about with the two windows so you can see them both easily.

To move a photo from “My Pictures” into “Holiday”: click on the photo, hold the left button of the mouse down, drag the photo across to the other window, and let go. This is easier to do if you can view your pictures as “Medium Icons” or “Large Icons” – somewhere on your window there will be a tab “View” which allows you to change the way you look at the pictures in the folder (it’s in different places depending on how your PC is set up). Try the various options and see what suits you. Note that if you are using Windows 7, the tab doesn’t say “View” instead it’s a little icon next but one to the blue question mark.

OK, so now you can drag your photos one by one from “My Pictures” into “Holiday”.
Feeling adventurous? Maybe you have six photos next to each other and want to move them all. Click on the first one, then hold down the Shift key and click on the last one. That should select the whole set. Hover the cursor over the first one again, hold down the left button, drag over to the other window, let go.
Now let’s do something a bit more whacky… Maybe you have six photos dotted about that all need to be moved. Click on the first one, hold down the CTRL key, and click on each of the others. Hover the cursor over the first one again, hold down the left button, drag over to the other window, let go.

Select multiple objects

If you want to select a set of objects that are adjacent, click on the first one, then hold down the SHIFT key, and click on the last one.  This will select the first, last, and all in between.  It works in Word and in Excel.  It also works with files; for example if you are looking at photos in My Pictures, and you decided to select a series of six photos that are next to each other, click on the first one, then hold down the SHIFT key and click on the last one.

If you want to select a set of objects that are not adjacent, click on the first one, then while holding down the CTRL key, click on the others one by one until you have selected the objects you want.

When you have your set of objects selected, go back to the first one, click on it and hold down, and then you can drag your objects around – to the desk top, to another folder, to the waste bin……