Excel User Group

Left Looking Lookups

Daily Dose of Excel — Fri, 10 Sep 2010 16:28:01 GMT

OR: The problem with VLOOKUP. Wikipedia gives us a table of birthstones, and I think just because it’s Wikipedia, there’s a trailing space after every entry. Pasted into a spreadsheet, the table looks like this: A B C D E 1 Month Traditional Birthstone(s) Modern Birthstone(s) Mystical Birthstone Ayurvedic Birthstone 2 January Garnet Garnet Emerald Garnet 3 February Amethyst Amethyst Bloodstone Amethyst ...(read more)

Office 2010 PIA Redistributable available for download!

VSTO & .NET & Excel — Thu, 09 Sep 2010 16:23:55 GMT

I know that some developers have been waiting to get access to Office 2010 PIA Restributable. Although we can create our own IA some developers prefer to use the officially PIA from Microsoft. Today when I checked it up I was pleasant surprise to find out that the PIA package is now available for download. [...] Read More......(read more)

True or false, IF provides the answer

Microsoft Excel 2010 — Wed, 08 Sep 2010 06:51:32 GMT

Today’s post is brought to you by Frederique Klitgaard. Frederique is a writer on Office.com It’s easy to understand why the IF function appears in the top 10 list of popular Excel functions. It’s a versatile function that can be used for all sorts of tasks. For example, this function can save you a lot of time when you need to grade a stack of papers. It quickly turns a long list of test scores into grades without having to do any of the math. It can also instantly determine...(read more)

Download our free Office 2010 migration guides

Microsoft Office 2010 Engineering — Tue, 07 Sep 2010 20:05:45 GMT

Over on the The Microsoft Office Blog there is a post that links to some migration guides for Microsoft Office 2010: “Whether you're coming from an older version of Office and just want to find the most frequently used commands in a program, or you're entirely new to Office and want a quick overview of how the ribbon works and how to get to the most important things, each guide provides helpful information that we've based on direct feedback from you — our customers.” Read more and download...(read more)

Building a self-sorting list (Part 2)

Daily Dose of Excel — Mon, 06 Sep 2010 14:04:33 GMT

In Part 1 we ended up with Column D, a sorted list. One criticism was the many times we were counting the numbers in a column. We should improve it and only count once. Via Insert/Name/Define define Count_BB to =COUNT(Sheet1!$B:$B), and then select Columns C:D, and “Replace All” COUNT(B:B) with Count_BB. [...] Read More......(read more)

Building a self-sorting list

Daily Dose of Excel — Fri, 03 Sep 2010 18:00:42 GMT

I haven’t posted in a good while. I’ve done over half the Euler problems, but haven’t had the time to research the high-numbered ones considering I might even understand them. Now that I’ve retired from my second career, and my third is only part-time, maybe I’ll get back to it. Today is [...] Read More......(read more)

Setting a Base Directory

Daily Dose of Excel — Thu, 02 Sep 2010 11:30:25 GMT

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. [...] Read More......(read more)

Count Active Customers

Daily Dose of Excel — Wed, 01 Sep 2010 11:30:50 GMT

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. [...] Read More......(read more)

=VLOOKUP (uhh…now what?)

Microsoft Excel 2010 — Tue, 31 Aug 2010 17:26:00 GMT

Today’s post is brought to you by Anneliese Wirth, a writer on Office.com. 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...(read more)

Dymo LabelWriter Part II

Daily Dose of Excel — Tue, 31 Aug 2010 11:30:33 GMT

A couple of weeks ago, I posted some code to print labels on a Dymo LabelWriter 450. I wanted to post the finished code because it has a few more tricks in it. Function PrintBoardFileLabel(ws As Worksheet) As Boolean Dim bReturn As Boolean Dim vaPrinters As Variant Dim i As Long [...] Read More......(read more)

Abbreviating Company Names

Daily Dose of Excel — Mon, 30 Aug 2010 15:12:18 GMT

Last week I was creating file folder labels with my new Dymo LaserWriter 450. The information on the folder label is serial number, part number, company name, and purchase order. Normally, this works great. However, I ran into one on Friday with a 30 character part name and a 29 character company [...] Read More......(read more)

Using SQL Server Compact Edition Database with Excel

VSTO & .NET & Excel — Sun, 29 Aug 2010 23:19:09 GMT

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 [...] Read More......(read more)

Bushy Trees

Daily Dose of Excel — Sat, 28 Aug 2010 05:37:06 GMT

A favorite peeve of mine is code with “bushy trees.” I first saw this phrase in Kernighan and Plauger’s Elements of Programming Style. Recently, I saw some code that checked if a RefEdit control referred to a single cell that contained a non negative integer. I cleaned up the formatting some since the original [...] Read More......(read more)

You Asked: What the heck is Scroll Lock

Microsoft Excel 2010 — Wed, 25 Aug 2010 06:52:37 GMT

This week’s post is written by Amy Miller. Amy is a writer for Office.com. She’s written and edited content for Excel, Access, OneNote, and InfoPath. Imagine you’re working in a spreadsheet and you innocently press the arrow keys on your keyboard to move to another cell, but instead of moving to another cell, the entire spreadsheet moves. You may have asked yourself, what the heck just happened, and how do I make it stop? Lucky you. You’ve encountered that pesky little problem called...(read more)

A Hundred Thousand Name Managers!

Daily Dose of Excel — Thu, 19 Aug 2010 07:55:15 GMT

Hi folks, Many of the regulars here probably know the Name Manager utility, which Charles Williams and I created and give away for free on our websites. Rumour has it this is one of best tools ever built for the Excel developer. I won’t argue with that! Anyway, as I was looking at my web stats today I [...] Read More......(read more)

Excel Web App leads to healthier snacks for kids

Microsoft Excel 2010 — Wed, 18 Aug 2010 22:00:00 GMT

Today’s blog post is brought to you by Roxanne Kenison. Roxanne is a writer on Office.com who writes primarily about Office Web Apps and Office Starter. My son starts Kindergarten this fall. I’m excited, but a little sad that our days of co-op preschool are over. Our local community college sponsors a co-op preschool program where parents run the preschool. Each parent works one day per week in the classroom, rotating through a series of jobs. We use an Excel spreadsheet to keep...(read more)

Showing Hidden Sheet and Workbooks Dialog in VBA

Daily Dose of Excel — Thu, 12 Aug 2010 11:30:54 GMT

A tip from Scott: Windows - Unhide, from the menu, shows the Unhide dialog box for unhiding workbooks. In vba, Application.Dialogs(xlDialogUnhide).Show gets the job done. Where it gets tricky is unhiding worksheets. Via the menu, Format - Sheets - Unhide In vba, the name of the dialog is not so intuitive. Application.Dialogs(xlDialogWorkbookUnhide).Show Poorly named, for sure, but you’ve been warned. [...] Read More......(read more)

Got lots of text in a cell? Make it readable

Microsoft Excel 2010 — Wed, 11 Aug 2010 21:10:00 GMT

Today’s blog post is brought to you by Gary Willoughby. Gary is a writer on Office.com who has created and edited content about Excel, Access, and Project. Sometimes, I find I have text in a cell that spans far past its own column or…yikes, maybe even off the screen. Because I tend to be a little borderline compulsive, that bothers me just a bit. Of course, if I autosize the cell by clicking the column border on its right edge, all the cells below that cell could end up being...(read more)

Printing to a DYMO LabelWriter 450 from VBA

Daily Dose of Excel — Tue, 10 Aug 2010 11:30:50 GMT

I recently had to make some file folders at work. About five minutes after I was done, I purchased a DYMO LabelWriter 450. I was using one of those label makers where you punch in the text, hit print, and press down on a lever to cut the label. Then you have [...] Read More......(read more)

Working with Folders

VSTO & .NET & Excel — Mon, 09 Aug 2010 20:48:34 GMT

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 [...] Read More......(read more)

Microsoft Office 2010 File Compatibility

Microsoft Office 2010 Engineering — Mon, 09 Aug 2010 20:40:24 GMT

Have you been looking for a tool to help you assess compatibility of your existing Office files and macros as you prepare to deploy Microsoft Office 2010? We have recently released the tool to help you identify potential concerns before you migrate. The Office Migration Planning Manger 2010, referred to as OMPM, has been released to help you assess and migrate documents for Office 2010. Download OMPM 2010 from the Microsoft download center, and yes it’s free. The OMPM tool has been created...(read more)

Contextual PivotTable Userform

Daily Dose of Excel — Mon, 09 Aug 2010 11:30:27 GMT

Sometimes when I write code, I feel as if I’m writing on a cloud with a unicorn’s horn dipped in angel tears. And sometimes I feel as if I’m using a sledge hammer. This is the latter. There have been some PivotTable shortcuts posted about (here, here, and here). When I get too [...] Read More......(read more)

Regular chart version of a sparkline

Daily Dose of Excel — Mon, 09 Aug 2010 05:58:06 GMT

In one of the social.answers.microsoft.com web forums (Microsoft’s replacement for the newsgroups no longer hosted on its own server) someone wanted to see a regular chart version of a sparkline when s/he clicked on the cell. I thought that was an interesting idea since one can see much more detail in a large object [...] Read More......(read more)

Lightening colors

Daily Dose of Excel — Fri, 06 Aug 2010 17:31:38 GMT

On an ongoing project, I had to create lighter shades of a color. Since we were working with Excel 2007, I decided to use Range.Interior.TintAndShade to achieve the desired result. TintAndShade is a number between -1 and 1 that lightens or darkens a color. Initial tests showed it seemed to work fine. [...] Read More......(read more)

Use SUMPRODUCT to find the last item in an Excel list

Microsoft Excel 2010 — Wed, 04 Aug 2010 17:00:00 GMT

Today’s author is Charley Kyd, an Excel MVP, who describes how to use the SUMPRODUCT function to find the last sequential item in an Excel list. Charley has worked with spreadsheets in business since 1979, and has written books on spreadsheet use for McGraw-Hill and Microsoft Press. You can find more useful tips and posts from Charley and other Excel MVPs at his website, ExcelUser.com . Suppose you have a Sequential List of items, as in the second table here. And suppose you want to show the date...(read more)