Publication Date: February 3, 2010
This newsletter is being sent to 14,000+ Excel 'addicts' around the world
using Aweber, the best solution for managing your email campaigns.
Hi fellow Excel Addict,
As I am preparing this newsletter tonight, the winter winds are howling outside my window. With half of the winter season behind us and very little snow so far, I think we are going to make up for it over the next day or two. The forecast is calling for 35 cm of snow and 90 km/h winds.
On the bright side, maybe we'll get a 'snow day' tomorrow!
I have an important follow up to a tip I gave you last week. In my tip How To Really Delete A File, I may have left the impression that holding down the Shift key while deleting a file would delete it permanently so no one could ever see it. The fact is, when you delete a file in this way (in fact, even when you empty the Recycle Bin), the files are still physically on your hard drive. However, they are marked for deletion and may or may not be overwritten by changes you've made since you deleted the files. Most users will not be able to access these deleted files, however, with the right software, these files could possible be recovered from the hard drive.
As you may have seen in the email I sent you earlier this week, the Excel Addicts Supporting Haiti campaign raised $5,000 to help out the earthquake victims. I will be making the $2,500 donation to the Red Cross in the next week or so after I receive a cheque for the proceeds of my ebook sales. The Canadian Government will be matching this donation with another $2,500. Again, I want to thank everyone who supported me in this campaign.
Keep on Excelling,
Francis J Hayes (The Excel Addict)
Please type 'The Excel Addict' in the Subject field of your emails.
If you reply to this message, please delete the newsletter content
below before sending. To remove the background color in
Outlook 2007, click Options, Page Color, No Color.
This week's tips..
1) Control The Order Pages Are Printed (XL2003/XL2007)
2) Create Dynamic Formula References Using Values From Cells (XL2003/XL2007)
3) Display Your Favourite Folders On Top
If you're having trouble displaying graphics in this email, I have posted an online HTML version of this week's newsletter here.
Last week's tips were...
1) Count How Many Times A Specific String Of Text Occurs In A Range (XL2000-XL2007)
2) Customize Your 'Open' and 'Save As' Dialog Boxes (XL2003-XL2007)
3) How To Really Delete A File
You can still find last week's newsletter here.
You can access even more tips on my website by going to my members' page.
Make sure you save a copy of your spreadsheet before trying these tips, just in
case you make an error or the procedure doesn't produce the results you want.
Control The Order Pages Are Printed (XL2000-XL2007)
Like most Excel users, if your data does not fit on one page, at least as far as it's width is concerned, you've probably figured out how to force it to fit either by changing the 'Scale to Fit' option on the Page Layout tab in Excel 2007 or the Page(s) Wide option in the Page Setup dialog to '1'.
Sometimes, however, you may not want to squeeze everything on one page, either because it prints too small or because the width of your report is such that it is not practical to print one page wide.
By default, when you print a worksheet that is more than one page wide, Excel prints from the top down and then left to right.
If your worksheet contains data that spans more than one page width you may prefer to print your report starting at the top left page, going right and then down.
In situations like this, you can control the order in which worksheet pages are printed
1) In Excel 2007, click the Page Layout tab. In the Page Setup group, click the Dialog Box Launcher (the small arrow in bottom-right corner of group); In Excel 2003, from the File menu select Page Setup;
2) Click the Sheet tab, and in the Page Order section, choose 'Down, then over' for top to bottom printing or 'Over, then down' for left to right printing. You will see a preview of the printing direction.
Create Dynamic Formula References Using Values From Cells (XL2003-XL2007)
about this, maybe you haven't, but I'll bet that once you see this you
will think of ways you can use this tip.
Pay close attention to the single quotes before the first square bracket and before the exclamation mark -- they are easy to forget.
PRACTICE: If you want to practice this in your own workbook, copy the following formula into your worksheet and replace the underscores with your own cell references.
The biggest drawback with the INDIRECT function is it's inability to access closed workbooks.
Also note that the INDERECT function does not work with dynamic named ranges such as in this example =OFFSET('Raw Data'!$A$2,0,0,COUNTA(AllNames)-1,1)
If you do need to use dynamic named ranges with the INDIRECT function, a possible solution is DINDIRECT, a User Definded Function (UDF) written by Dick Kusleika at Daily Dose of Excel.
If you're not 100% satisfied, simply request a full refund from ClickBank.com
Display Your Favourite Folders On Top
When you look at all the folders in your My Documents folder, you probably see a hodge-podge of familiar and unfamiliar names. Often when you install a new program it will add a folder to your My Documents folder (e.g. ArcSoft ToGo, My Skype Pictures, etc...). Over time there can be dozens of subfolders created in your My Documents folders.
In most cases, you are using only a few of these folders regularly. Looking for a particular folder in this mess can be frustrating. You can make it a lot easier to find your favourite folders by moving them to the top of the My Documents folders.
You can do this by simply adding a number prefix to the beginning of each folder's name (i.e. 01. Finances, 02. Homework, 03. Recipes, etc...) and when they are sorted, your favourite folders will be on top. If you don't put the 0 before numbers 1 to 9, you folders won't sort properly when you have 10 or more folders.
This same trick can be used to force specific filenames to always be at the top of the list (i.e. 01. Chequing Account.xlsx, 02. Savings Accounts.xlsx, 03. Credit Cards.xlsx, etc...).
Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter.
If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com
If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com
3) Post a link to TheExcelAddict.com in a company newsletter or website
|"Spreadsheets Tips From
An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630