Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Building a self-sorting list (Part 2)

    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...
    Filed under:

  • Building a self-sorting list

    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...
    Filed under:

  • 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. [...] Read More...
    Filed under:

  • 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. [...] Read More...
    Filed under:

  • Dymo LabelWriter Part II

    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...
    Filed under: , ,

  • Abbreviating Company Names

    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...
    Filed under:

  • Bushy Trees

    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...
    Filed under: ,

  • A Hundred Thousand Name Managers!

    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...
    Filed under: , , ,

  • Showing Hidden Sheet and Workbooks Dialog in VBA

    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...
    Filed under:

  • Printing to a DYMO LabelWriter 450 from VBA

    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...
    Filed under: ,

  • Contextual PivotTable Userform

    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...
    Filed under:

  • Regular chart version of a sparkline

    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...
    Filed under:

  • Lightening colors

    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...
    Filed under:

  • The True Cost of Spreadsheets

    Rob wrote a white paper called Spreadsheet Addiction: The True Cost to the CPG Industry. In it, he lists the reasons why Excel is so popular and widely used and he lists the reasons why it costs more than you think. I’d excerpt some of that text, but I don’t know how to [...] Read More...
    Filed under: ,

  • Toggle PivotFields from Count to Sum

    Carrie laments: Is there a way to change the default in a pivot table so sums the values when created rather than count? It is very time consuming if you have several data points. I hear ya, sister. Excel does a pretty good job guessing, but sometimes it gets stuck on Count like that’s all [...] Read More...
    Filed under: ,
1 2 3 4 5 Next > ... Last »
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.