 |
||
Publisher's NotesThe Thanksgiving holiday is fast approaching and will be celebrated in many countries this coming Thursday, November 26. I hope that you have a great holiday and that you are able to spend it with those who matter most in your life. This last week there was also a bit more feedback about experiences teaching Excel. Peg (from Michigan) pointed out the importance of not overwhelming students with the many ways that Excel can accomplish a given task. Even though you can accomplish a task using the menus, ribbons, toolbars, macros, or shortcut keys, that doesn't mean you have to show all of those methods at once. Do so and you may find yourself looking at a sea of confused faces. It is far better to pick one method, teach that method, and then branch out to other methods as the students are ready to absorb more. In the frustrations category, Steve (from Ohio) indicated that one of his biggest headaches is teaching clients how to properly work with ZIP Codes in Excel. When working with ZIP Codes that have leading zeroes, it is easy to lose them if you don't prepare the worksheet and/or the data being used. Steve's not alone in this area; I regularly have data that needs those leading zeroes restored before I can finish a project. The compilation of the video footage I shot a few weeks ago at an Excel seminar I was teaching continues apace. (Shooting the video is just the first step. There's always a lot of work to do after that step is complete.) I'm hoping that I'll have something to announce in this regard in the next few weeks—perhaps as early as the first few days of December. My hope is that others who need to teach (or learn) Excel can benefit by such a "video seminar" that can be viewed whenever necessary. I'll keep you informed as we get closer to having something ready. In the meantime, if you still have any ideas on teaching (or common teaching frustrations), send them my way and I will keep right on sharing. —Allen TipsIf you have an idea for a tip, send it our way. Any tips contributed will be credited in the issue in which they appear. You can learn more about the tips in this week's issue of ExcelTips by clicking the links provided below. Changing an Invalid Autosave FolderExcel allows you to specify where it stores various files used by the program. One location you can specify is where Autosave files should be saved. If you specify a folder that later becomes unavailable, however, you could end up with a headache. (Thanks to John Madden, Ron Solecki, Barry Dysert, Kathy Brancato, Hans Remiens, Bill Cohen, and John Keegan for contributing this tip.) Replacing Links with ValuesNeed to get rid of the links in your workbook but save the values that were retrieved by those links? It could be easy or hard, depending on the complexity of your workbook. Here are some great ideas you can try. (Thanks to Rafaqat Choudhry, Sayed Aty, Andrija Vrcan, Steve Aprahamian, Simon Hampton-Matthews, Bhavik Khatri, Avigdor Shore, Richard Raciborski, Kanti Chiba, Gretchen Uhas, Graeme Wiltsher, James Benton, Mark Mace, Susan Dye, Hanlie Croeser, Chris Yu, and Adam Pope for contributing to this tip.) Pay a Visit to FormvilleNeed business forms you can put to use right away? How about if you can get those forms for free? Discover what awaits you in Formville, your destination for free printable business forms. Converting to OctalIf you need to do some work in the base-8 numbering system (octal), you'll love two worksheet functions provided by Excel for this purpose. These functions allow you to convert values to octal and convert them back again. Determining if Calculation is NecessaryWhen processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn't it be nice if you could only recalculate if Excel tells you that recalculation is necessary? Here's some ideas on how you can figure out when you need to do a recalc. Need More ExcelTips?You are reading the free version of ExcelTips. There is another version you could be reading right now: ExcelTips Premium. This week ExcelTips Premium subscribers also learned about:
Each weekly newsletter is in a professional PDF layout and presents double the tips (Premium subscribers see articles you won't in regular ExcelTips) with great, useful graphics. Plus, ExcelTips Premium subscribers get valuable money-saving benefits. For more information (including a sample issue), visit the Tips.Net store. Help WantedThis section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question. If you have a solution for the problems below, send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer. Using a Macro to Select a Modified Table BodyI have an Excel 2007 table defined (via insert -> table) and I want to select just the data portion of the table using VBA. I know I can use the DataBodyRange.Select method, but this just seems to select everything apart from the header row. In my table the first row contains headings, the last row and last column contain formulas, and the first column contains row headings, so I want to exclude these from the selection. The table can expand both by rows and columns, so I need some way to select this data dynamically. Any thoughts on how this can be done? |
||
 |
||
 |
||
Publisher and Copyright Information |
