Spreadsheet Tips From An Excel Addict Helping Average Spreadsheet Users Become Local Spreadsheet Experts A Free Weekly Publication of TheExcelAddict.com   Publication Date: June 28, 2006 This newsletter is sent only to those who have requested it. If you didn't request this, see the simple instructions at the bottom of this newsletter. GREETINGS FROM CANADA   Hi fellow Excel Addict,   Welcome to another edition of 'Spreadsheet Tips From An Excel Addict'. Let me know if you find these tips helpful. I like hearing about the different ways people use Excel.   As I often say, 'Make sure you practice these tips. Don't just read it and assume that one doesn't apply to you. When you actually practice the tip, it may trigger ideas of how YOU can apply it'. If you keep this in mind each week, you will benefit much more from reading my newsletter.   Hope this week's tips trigger new ideas for you.   To your success, Francis J Hayes (The Excel Addict) THIS WEEK'S QUOTE: "If you want your life to be more rewarding, you have to change the way you think." -- Oprah Winfrey This weeks tips... Using A Data Validation List From Another Sheet Custom Menu Access To Frequently Used Workbooks Put An End Long Lines In Email Messages Last weeks tips... Generate Random Numbers A Virtual AutoFilter Toggle Button Quick Webpage Shortcut You can access my archive of tips on my website by going to http://www.theexceladdict.com/members.htm. Please remember to include "TheExcelAddict" in the Subject line of any emails you send to me at fhayes(AT)theexceladdict(DOT)com. This will help your email stand out in the flood of junk email I receive daily. You can help! Do you know other Excel users who would benefit from these tips? Go ahead, email this newsletter right now to the ones you know. As you know, I really love using Excel and sharing my tips with everyone who can benefit from them. My goal is to have my weekly tips going out to 1,000,000 Excel users all around the world. I would really appreciate it if you could help me reach this goal by sharing this newsletter with all of your 'Excel-loving' friends and colleagues. You will be helping me and also everyone you share my tips with. Thanks in advance for your support. Francis J Hayes (The Excel Addict) EXCEL SCREENSAVERS: Don't forget to check out the cool Excel screensavers in the new downloads section of my website. http://www.TheExcelAddict.com/downloads Looking to take your Excel skills to the next level? Check out... EXCEL "QUICK TIP" Using A Data Validation List From Another Sheet   When you attempt to use a list from another sheet as the source for Data Validation, Excel appears not to allow this. There is workaround however. The trick is, you must give the list a name (Insert, Name, Define) and then use the defined name as the source for your data validation list.   For example, say you have cells on Sheet1 whose input you want restricted to a specific list of values. You have decided to keep the validation list on a separate sheet, Sheet2.   Go to Sheet2 and select the list of values; From the Insert menu, select Define, Name; Type a name (i.e. MyList) and click OK; Go to Sheet1 and select the cells whose values you want restricted; From the Data menu, select Validation; In the Allow dropdown box, select List; In the Source field, type the name of your list preceded by an equal sign (i.e. =MyList).   What is Data Validation? Data validation (Data, Validation) is a tool in Excel that gives you control over the kind of information that is entered into specific cells/ranges in your worksheet. Data validation enables you to provide users with a list of choices, restrict entries to a specific type or size, or create custom settings.   Know an even better way? Let me know at fhayes(AT)theexceladdict(DOT)com Click here for more Excel "Quick Tips". Simplify your daily office tasks! PowerPlugs: Excel Solutions for Office Managers is a collection of professional office forms and spreadsheets. There are 21 useful forms. All conveniently work inside Microsoft Excel. They are designed to simplify your daily office tasks and help you run your business more smoothly. Take a look here! "HOW TO EXCEL" MINI-TUTORIAL Custom Menu Access To Frequently Used Workbooks Most of us Excel users have a number of workbooks that we use frequently. Maybe you have already placed shortcuts on your desktop or the Start menu for quickly accessing these files. Or maybe, like most people, you go through File, Open and browse to the folder and then open them. One of my newsletter readers, Ken Gingrich, sent me a great tip for getting quick access to your most frequently used workbooks. This is really cool! Basically what you do is add a custom menu to the top of you Excel screen between Window and Help and then add custom buttons that are hyperlinked to frequently used workbooks. In the example below we will name the new menu Favorites. Then, to open a file, you just click the Favorites menu and select a workbook from the list. So let's try it. You're gonna love this! Right-click anywhere on your Excel toolbars. Select Customize..., and click the Command tab. From this point forward the Customize dialog must be open. Scroll down the Categories list and select New Menu. Notice to the right, in the Commands section, there is one item listed called New Menu. Drag this item up to the top of your Excel screen and drop it between the Window and Help menus. Now, right click on this new menu and you'll see a Name option where you can rename it. Change the name to Favorites and press Enter. Next we will add a button and hyperlink it to a workbook. Back in the Customize dialog box, in the Categories section, select Macros. From the Commands section on the right, drag the Custom Button item up to your new Favorites menu. Hover the cursor over the Favorites menu and you'll see a blank dropdown below the menu. Carefully move the cursor until you see the insertion point in the blank dropdown. Drop the Custom Button here. To hyperlink a file to this button, click on the Favorites menu, right-click on Custom Button, move down to Assign Hyperlink and click Open. Browse to the workbook you want to hyperlink, select it, and click OK. Right-click on Custom Button again, and this time type a name for your workbook, and press Enter. Repeat steps 7 to 10 for each workbook/custom button you want to add to the Favorites menu. When you are finished, Close the Customize dialog box. I recommend that you close and reopen Excel now so your new menu will be saved as part of Excel's toolbars. Now, is that a great idea or what? Thanks Ken for the suggestion. Know an even better way? Let me know at fhayes(AT)theexceladdict(DOT)com Click here for more "How To Excel" Mini-Tutorials. EXCEL ADDICTS AROUND THE WORLD Every week this newsletter is being read by Excel Addicts in 100 countries: Albania, Anguilla, Antarctica, Antigua and Barbuda, Argentina, Aruba, Australia, Austria, Azerbaijan, Bahrain, Barbados, Belgium, Bosnia and Herzegowina, Brazil, Canada, Cayman Islands, Chile, China, Colombia, Costa Rica, Cote d'Ivoire, Croatia, Cuba, Cyprus, Czech Republic, Denmark, Dominican Republic, Ecuador, Egypt, El Salvador, Estonia, Finland, France, Germany, Ghana, Greece, Guam, Hong Kong, Hungary, India, Indonesia, Iran, Ireland, Israel, Italy, Jamaica, Japan, Kazakhstan, Korea, Kuwait, Lebanon, Macau, Macedonia, Malaysia, Mali, Malta, Mexico, Nepal, The Netherlands, New Zealand, Nicaragua, Nigeria, Norway, Oman, Pakistan, Papua New Guinea, Paraguay, Peru, Philippines, Poland, Portugal, Puerto Rico, Qatar, Reunion, Romania, Saudi Arabia, Singapore, Slovenia, South Africa, Spain, Sri Lanka, Sweden, Switzerland, Tajikistan, Tanzania, Thailand, Trinidad and Tobago, Turkey, Uganda, Ukraine, United Arab Emirates, United Kingdom, United States, Uzbekistan, Vanuatu, Venezuela, Viet Nam, Yugoslavia, Zambia, Zimbabwe. There are tens of millions of other Microsoft Excel users all over the world. The vast majority of them have a very limited knowledge of Excel's capabilities. My goal is to share this information with everyone I possibly can and help them to benefit from Excel's amazing capabilities. Please help me spread the word about... "Spreadsheet Tips From An Excel Addict". BONUS "NON-EXCEL" TIP Put An End Long Lines In Email Messages If you use Outlook as your email program you may have noticed a message at the top of each email saying "Extra line breaks in this message have been removed." If you'd prefer to see your email message the way it was intended (i.e. with shorter lines) you can click on the message at the top of the screen and you'll be given an option to "Restore line breaks".   If you want to permanently 'fix this', here's how. From the Tools menu, select Options and click the Preferences tab. In the E-mail section click the E-mail options button and uncheck the "Remove extra line breaks in plain text messages" option.       Know an even better way? Let me know at fhayes(AT)theexceladdict(DOT)com Click here for more "Non-Excel" Tips. SUBSCRIPTION MANAGEMENT Spreadsheet Tips From An Excel Addict is available only to subscribers of my free email newsletter. If this newsletter was forwarded to you and you would like to get your own subscription, please send a blank email to TheExcelAddict(AT)Aweber(DOT)com "Spreadsheets Tips From An Excel Addict" is a FREE 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