Subscribe in a readerExcel Headlines
My very own list of news on Excel, gathered from various sources!
This site
| Updated article: Preventing auto_open and Workbook_Open events from running Seems the methods I described to prevent the workbook_Open event do not always work for Excel 2007. Thu, 08 Jul 2010 20:00:00 GMT |
| Name manager version 4.2 released Name Manager for Excel has been updated to version 4.2 with quite a number of updates. Thu, 01 Apr 2010 17:50:00 GMT |
Daily Dose Of Excel
By Dick Kusleika (et al)| Setting a Base Directory If you want your Excel app to default to a specific directory when opening or saving files, see Changing the Current Directory. Be sure to read the comments. I have a slightly different situation. I want my app to open to a specific directory that will give me easy access to sub directories. [...] Thu, 02 Sep 2010 11:30:25 +0000 |
| Count Active Customers Jake wants to know, given an active date and an inactive date, how to count the customers that were active in a certain time period. The ones we want are highlighted in yellow. The formula is =COUNT(D2:D21)-SUMPRODUCT(($C$2:$C$21>=D25)+($D$2:$D$21< =C25)) It’s easier to figure out who is not active during that date range and subtract it from the total. [...] Wed, 01 Sep 2010 11:30:50 +0000 |
The Ken Puls Blog
By Ken Puls| Enabling Outlining Commands on a Protected Worksheet I have a financial model that I set up using a grouping in some key places so that I could collapse sections of the model when I didn’t want to look at them. As I was handing off the model to someone else to work with, I wanted to protect the worksheets, but unfortunately [...] Thu, 02 Sep 2010 07:18:15 +0000 |
| Macro to wrap an existing formula with IFERROR Today I realized that one of my GETPIVOTDATA formulas was returning an error, since there wasn’t any result in the Pivot Table it was checking for data. Unfortunately, during the design of the spreadsheet I never wrapped the function with IFERROR to avoid this. The challenge I had was that I had several different formulas [...] Fri, 27 Aug 2010 21:17:34 +0000 |
.NET & Excel
By Dennis Wallentin| Using SQL Server Compact Edition Database with Excel Introduction Often we see questions about acquiring data from various data sources like SQL Server Databases, MDBs, Excel and text/xml-files in webbased Q&A forums and in blogs. But what I see very rarely is any discussions on how to retrieve data from Microsoft SQL Server Compact Edition (SSCE) databases and to populate Excel worksheets with [...]  Sun, 29 Aug 2010 23:19:09 +0000 |
| Working with Folders Introduction It’s quite often that we need to work with folders in various Excel solutions. It involves creating, deleting, moving, copying and update folders with or without any user interactions. In this article I will discuss and show the code in order to work with folders. In the first part I will cover the basic [...]  Mon, 09 Aug 2010 20:48:34 +0000 |
PTS Blog
By Jon Peltier| Fake Line Chart (Dummy XY Series for X Axis) In Excel, the difference between Line charts and XY charts has nothing to do with formatting the data with or without lines, and everything to do with different behavior of the X axes in the charts. I’ve written about these differences numerous times, in X Axis: Category or Value?, Scatter Chart or Line Chart?, Line-XY [...] Tue, 31 Aug 2010 07:00:43 +0000 |
| Custom Error Bars in Excel Charts I’ve written about Excel chart error bars in Error Bars in Excel Charts for Classic Excel and in Error Bars in Excel 2007 Charts for New Excel. Both articles contained instructions for adding custom error bar values for individual points, but judging from the emails I receive, a separate article on custom error bars is [...] Mon, 30 Aug 2010 07:00:18 +0000 |
Contextures Blog
By Debrah Dalgleish| Combine Data From Two Excel Files in Pivot Table On Monday, Excel MVP Kirill Lapin (aka KL) shared his macro to create a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook). I promised you a second pivot table macro, and here it is -- in today's example, Kirill combines data from a sales list and price list, stored [...] Wed, 01 Sep 2010 04:02:00 +0000 |
| Macro Creates Excel Pivot Table From Multiple Files If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality. Last year, Excel MVP Kirill Lapin (aka KL) shared his brilliant code to create a Union query and build a fully functional [...] Mon, 30 Aug 2010 04:02:00 +0000 |
Professional Office Developers Association's Blog
By various Authors An error has occured while trying to process http://feeds.feedburner.com/ThePodaBlog?format=xml
Please contact web-master.
Putting You & I Back Into Office 2007 UI
By Patrick Schmid| Outlook 2010: 7+ Home tabs Outlook 2010 has 7+ tabs labeled Home. Let me introduce them to you (none of them were taken with an Exchange account; apologies for the varying screen resolutions): Mail Mail - IMAP Note: Quick Steps don’t show in the technical preview for IMAP due to a bug Mail - RSS Calendar Calendar - List view Contacts Tasks Notes Journal Sun, 02 Aug 2009 11:36:47 +0000 |
| Computer woes Part of the reason why it has been quiet on the RibbonCustomizer front is that the mainboard of my main computer (a good old Gigabyte mainboard with an AMD 64) died after years of faithful service. Unfortunately, replacing my computer with a brand-new Core i7 based machine became a nightmare on its own. Used to the [...] Tue, 28 Jul 2009 12:11:16 +0000 |
Microsoft Excel on MSDN
By various Authors| =VLOOKUP (uhh…now what?)
On Office.com, the term “VLOOKUP” frequently bubbles up in our list of high-volume search queries. My job is to figure out why this is and what specific information people are actually looking for when they type “VLOOKUP” into the search box in Help or on our web site. The “VLOOKUP” query appears to represent a bunch of different questions. Certainly, one of the main ones has to do with the function arguments—a.k.a., “the stuff between the parentheses." The bottom line is this: If you don’t use VLOOKUP regularly, it’s hard to remember off the top of your head what the different arguments mean, or how to use them correctly. From personal experience, I can really relate. I use VLOOKUP infrequently, usually when I need to run a particular type of report for my writing team. The report requires me to pull data from my content management system, and then match topic ID numbers in one worksheet to corresponding titles in another worksheet. To make things work, I invariably end up in Help, poring over our VLOOKUP topic. In particular, as an occasional user of VLOOKUP, I wonder about the following: · What pieces of the formula do I need, and in what order? · What’s the secret handshake for referencing lookup tables on other sheets? · Is TRUE the right argument for exact matches? Or is it FALSE? Which one is the default? Which is the one that requires me to sort the first column in the lookup table in A to Z order? · When counting columns, do I start at 0 or at 1? · Why am I seeing the wrong results when I copy the formula? To help address questions like these, I created a dedicated VLOOKUP Refresher. Feel free to print a copy of this card or save it for later, whenever you need it.
Of course, anyone who uses VLOOKUP will eventually want to know THIS, too: · Why am I getting #N/A errors? That last bullet is a whole ‘nuther ball of wax. I’m currently working on a card of troubleshooting tips and techniques for circumventing #N/A, but in the meantime, check out Greg Truby’s recent post, Solutions to three common problems when using VLOOKUP(). If you have comments about the VLOOKUP Refresher card, tips or resources of your own, or suggestions for future posts, please leave a comment. Tue, 31 Aug 2010 17:26:00 GMT |
