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

January 2011 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • TM PivotTable Dashboard

    A client wanted to visualize a table of data with a dashboard where each chart represented specific information in the table. I had two choices: 1) Develop a client-specific solution and charge the client the entire development cost, or 2) develop a more general solution, market it to a broader customer base and charge the client [...] Read More...

  • Dynamic Matrix with ADO

    Yesterday I created a matrix with arrays. I didn’t love the code and hoped to make something more manageable. Since I sorted the array two different ways, I thought a disconnected recordset might help clean up the code. Sub MakeDynamicADO()         Dim rs As ADODB.Recordset, rsFilter As ADODB.Recordset     Dim rCell As [...] Read More...
    Filed under:

  • Dynamic Matrix

    A friend of mine asked me to turn this A B C 1 Developer Task # File 2 Evelyn 74333 A 3 Ava 60490 A 4 Carlos 52120 A 5 Andrew 20392 B 6 Andrew 57972 B 7 Andrew 20392 C 8 Payton 29506 C 9 Rachel 25639 D 10 Rachel 14330 D 11 Andrew 51722 E 12 Andrew 20758 E 13 Andrew 51722 F 14 Andrew 20758 F 15 Andrew 51722 G 16 Andrew 20758 G into this I busted through the code and, while it works, I’m not very pleased with it. It’s too long and has too many loops. Take a look. Sub MakeMatrix()  ...
    Filed under:

  • Set Default Number of Copies

    I have this workbook that, when I try to print, defaults to printing 14 copies. Take a guess how many times I have to use my printer’s cancel button. If you guessed “every time”, then you guessed right. To fix this, I went to File - Page Setup and clicked on the Options button I [...] Read More...
    Filed under:

  • Formula Challenge

    I don’t know how to comment on Chandoo’s site, so I’ll post it here. I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help me with a formula for this? Here’s my contribution. A bit long. =IF(ABS(LARGE(A1:C1,2)-MAX(A1:C1))<abs (LARGE(A1:C1,2)-MIN(A1:C1)),AVERAGE(LARGE(A1:C1,2),MAX(A1:C1)),AVERAGE(LARGE(A1:C1,2),MIN(A1:C1))) I figured [...] Read More...
    Filed under:

  • Access Love

    Some Access love CattleMax is developed in Microsoft Access 2007. Access has been a key ingredient to our desktop software’s success. A lot of developers don’t give Access the credit it deserves as a powerful and rapid development tool. We have done extensive customizations to our interface to differentiate from the Access default templates and many [...] Read More...
    Filed under:

  • Lotus Notes Mail Code

    FYI Excel MVP Dennis Wallentin allow me to publish his Lotus Notes examples on my site. Great to have them online again so I can point Lotus Notes users to them when they mail me. http://www.rondebruin.nl/notes.htm Have a nice day Regards Ron de Bruin http://www.rondebruin.nl/tips.htm Read More...
    Filed under:

  • Send an Email to GoodTodo

    Here’s your Daily Dose of Outlook. There are a few things I don’t like about forwarding messages to GoodTodo. I don’t like the FW in the title indicating it was forwarded. I don’t like that I have to delete my signature from the newly created message. I don’t like that I [...] Read More...
    Filed under:

  • Slice and Dice!

    Hi all, I had a bit of time to spare the other day (got snowed in during travel by train) and wrote a couple of pages about a new Excel 2010 feature: Pivot table slicers I show how to customise them and also included some VBA examples on how to work with them. Enjoy the read! Regards, Jan Karel Pieterse www.jkp-ads.com Read More...

  • Distributing controls in a userform

    On many occasions there is a need to lay out controls horizontally in a userform so that they are equally spaced and, as a group, centered within the userform. An example is the group of three buttons (OK, Cancel, and Help) in the userform below. The worksheet below calculates the left position of each of [...] Read More...

  • How I Do

    From Walkenbach and Doctorow. Desktop: HP HPE-150f, i7 860@2.80, 8G RAM, Win7 64bit, Dual 22″ Acer monitors. I used to be strictly a laptop guy, but when the D810 died, I decided to go for a desktop. I’m happy I did. It’s quite a bit faster for the money. Laptop: ASUS R1F Convertible tablet, [...] Read More...
    Filed under:

  • Normal Winners

    Recently, 1,158 participants selected the winner, against the spread, of 19 NCAA College Football bowl games. I, being one of those participants, selected 8 correct winners - not my best effort. Let’s see how everyone else did. And the data Wins Players Normal 0 0 0 1 0 0 2 0 0 3 1 2 4 6 7 5 31 22 6 77 55 7 145 108 8 196 170 9 217 213 10 209 213 11 160 170 12 68 108 13 38 55 14 6 22 15 4 7 16 0 2 17 0 0 18 0 0 19 0 0 1158 1154 The formula in column C is =ROUND(NORMDIST(A2,AVERAGE($A$2:$A$21...
    Filed under:

  • LoadPictureGDI updated for Excel 2010 64 bit

    Hi all I update my GetImage page for Excel 2007-2010. Thanks to Rob Bovey LoadPictureGDI (from Stephen Bullen) is also working now in Excel 2010 64 bit. You can find the code in the example workbooks on the page below http://www.rondebruin.nl/getimage.htm FYI : Because of a bug in Word 2010 the code is not working in Word 2010 for ribbon [...] Read More...
    Filed under:
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.