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

March 2010 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Copying Worksheets with VBA

    An Excel question via email I have one workbook, workbook1 where i have saved all the names of worksheets saved in my workbook. These are in range of B2:B20 Now I want to run a loop which will select the name from the range and copy all data from that worksheet to another workbook, workbook2 with [...] Read More...
    Filed under:

  • Six

    In honor of DDoE’s sixth birthday, I thought I’d see how this blog is developing compared to other six-year-olds. From About.com: At mealtimes, the six-year old has a very good appetite - I’ve been starving this blog for years, so no match there. Very few six-year olds will nap - This blog just took a week [...] Read More...
    Filed under:

  • Maxif and Minif with Multiple Variables

    In Maxif, Minif Functions, I discussed finding the maximum or minimum for a subset of data based on certain criteria. If you have more than one criterion, the formulas are roughly the same. In this example, I have three items in four different PriceZones. I want to find the most expensive and least expensive [...] Read More...

  • PowerPivot – Part 4 of 4: A geographic visual display of 18 million US Census records

    Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/ Part 2: http://www.dailydoseofexcel.com/archives/2010/02/27/powerpivot-part-2-of-4-prepping-the-census-data/ Part 3: http://www.dailydoseofexcel.com/archives/2010/03/02/powerpivot-part-3-of-4-conditional-shape-colors/ In this tip I discuss integrating the result of a PowerPivot analysis of a large data set (18 million records) into a geographic map using a method I call “Conditional Color of Shapes.” For a version...
    Filed under:

  • Charting House

    I was watching a DVR’d episode of House recently and I became aware that the amount of show between commercials was changing as the show went on. The commercials were getting closer together. Then at the end, a long run of show between the last commercial and the credits. I did what [...] Read More...
    Filed under:

  • Language Detection

    Carim asked an interesting question: Do you think a UDF could determine the string language … ? In Switzerland, there are three national languages (German, French, Italian) and it would be very handy to have an UDF return in which language a given string is written My first thought was to create a group of common words in [...] Read More...
    Filed under:

  • Importing text files in an Excel sheet

    Hi there! Many times it is a newsgroup post which triggers me to do a writeup on a specific subject. This time, a user asked how he could import a csv file every month, without having to go through the hassle of renaming the file and re-defining the import settings each time. Opening the file in Excel [...] Read More...

  • Using the MATCH Function for a Linear Search and a Binary Search

    Given the problem I have with images and blog posts, I’m trying something new. I uploaded the content of the post to my website and am using an iframe to post it here. Yes, the UI experience is somewhat different than scrolling the entire browser window but hopefully it is something most people [...] Read More...
    Filed under: ,

  • Keno Odds

    We were trying to figure out the odds on keno the other day. There’s something about statistics that doesn’t create appropriately strong synapses in my brain. I got an A in stats in college, so I know I can retain the information long enough to be tested on it. But put me [...] Read More...
    Filed under:

  • Getting Array Data from a Filtered List in VBA

    Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this: vMyArray = Sheet1.UsedRange.Value I thought it would be keen to fill an array from a filtered list, so I coded Sub ArrFilteredList()         Dim vArr As Variant         vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value       [...] Read More...
    Filed under:

  • Plain and Pretty

    Mike Alexander tells me that I have to sell, so sell I shall. First, the pretty: Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together again to bring you our acclaimed Excel Dashboard and Visualization Bootcamp! This 3-day boot camp is designed for Excel users who need to more effectively synthesize [...] Read More...
    Filed under:

  • Office Automation

    Someone asked if anyone automates Office applications any more. That struck me as kind of funny. If I had to guess, I would guess that there are more lines of VBA code (poorly written or not) that automate Office applications than all the COBOL, VB6, PHP, and any other language you can think [...] Read More...
    Filed under:

  • PowerPivot - Part 3 of 4: Conditional Shape Colors

    Part 1: http://www.dailydoseofexcel.com/archives/2010/02/26/powerpivot-part-1-of-4/ Part 2: http://www.dailydoseofexcel.com/archives/2010/02/27/powerpivot-part-2-of-4-prepping-the-census-data/ As mentioned in the Part 1, I had developed this technique to conditionally color shapes some time ago. Based on customer feedback and my own experience I made changes to simplify the various connections required to make it all work. Also, documenting the technique was easier to do with a webpage [...] Read...
    Filed under:

  • Web Query Lessons

    I’ve recently learned some things about Web Queries that I’d like to share with you. But first a little background. You create a Web Query in Excel 2007 by clicking From Web on the Data tab. This launches a browser within Excel through which you navigate to a web page and select a [...] Read More...
    Filed under:

  • IsHex Function

    I have a need to verify some user input is hexadecimal (0-9 and A-F). Public Function IsHex(sInput As String) As Boolean         Dim lResult As Long         On Error Resume Next         lResult = CLng("&H" & sInput)             IsHex = sInput = "0" Or lResult > [...] Read More...
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.