• Ken Puls

    by Published on 2012-01-22 05:52 AM     Number of Views: 38282 

    Over several years of participating in forums, and working on my own projects, I always felt it was a bit awkward to create and send new emails through Excel. Invariably, every time I found that I needed email code, I ended up heading off to a site to copy an example (usually from my colleague Ron de Bruin's excellent site), then customizing to make it work.

    The goal of this article is to provide an even easier way to add email functionality to your Excel (or any other Office) project... something easy enough for beginner coders to use as effectively as master coders. I wanted a re-usuable chunk that I could just drop into my project with ease, and I believe I've accomplished that here.
    by Published on 2011-12-06 05:36 AM     Number of Views: 13790 

    If you'd like to prevent anyone from printing your workbook, this code will do the trick (subject to the caveat below).
    by Published on 2011-11-11 07:56 AM     Number of Views: 18719 

    My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that "Green means go", and make sure that any cell they enter data in has a green background. I also use blue backgrounds for "update these sometimes" cells, like tax rates. If cells are left with no colouring, though: everyone here knows that they should be left alone.

    Often, I have a few data entry cells or blocks on multiple worksheets. While this follows good spreadsheet design principles, it does have a bit of a side effect: When you print the worksheets, the green backgrounds print as well. While they're great for telling the user where to input data manually, it's distracting when you are looking at a printed (presentation version) of your data or report. So the question you may be asking is what can we do about it?

    We could create a full new report that essentially duplicates what we've got, but then it's both a maintenance headache and performance hit. Instead, we can use a conditional format ...
    by Published on 2011-11-04 04:04 AM     Number of Views: 92094 

    Reference Sites
    • Anne Troy's Office Articles
      • Anne has over 230 articles and tutorials available for the MS Office suite! Well worth the read!

    • Andy Pope's "AJP Excel Information"
      • Andy's site is very heavy on Charts, with lots of great pictures and examples. He's also got a collection of Excel based games as well.

    • Bob Phillips xlDynamic
      • Bob's site contains very comprehensive papers on Excel formulas and VBA. My personal favourite is his explanation of Multiple Condition tests using Sumproduct.

    • Chip Pearson's Excel Information
      • Chip has an incredible amount of information on his site, including using the Windows API to get at things not accessible through VBA alone.

    • Colo's Junk Room
      • Colo has all kinds of neat things at his site, including a Class Module tutorial, and tips for teaching VBA programmers how to program in javascript. (This site went offline back in 2011, but the link above accesses the most recent copy via the Wayback Machine.)

    • Deskbright
    by Published on 2011-11-03 07:37 AM     Number of Views: 10832 

    The following are some of the forums and usergroups that I've been know to frequent:
    by Published on 2011-11-02 06:31 AM     Number of Views: 40690 

    I am quite grateful for all the great feedback I've received from my many years in the community working on projects and training. Below are a few excerpts/quotes from some of my particular favourites.

    NASA - International Space Station Power Systems

    • The team has created a tool that takes their analysis info and puts it all into excel. This has allowed them to chart their information, making
    by Published on 2011-11-01 07:40 AM     Number of Views: 150914 

    Excel Blogs
    • The Ken Puls Blog
      • Okay, it's a shameless plug, but this is my own blog, which serves as my testing ground for Excel related material, among other things.

    • Bacon Bits
      • Mike Alexander's Excel Blog. Excel during the week and bacon recipes on the weekend

    • Contextures
      • Debra Dalgleish's Blog focussing on Office and productivity

    by Published on 2011-10-24 03:21 AM     Number of Views: 191514 

    This page lists many of the books I recommend.

    In the interests of full disclosure here, each of these images contains an affiliate link, so I do make a (small) amount of money if you click through and purchase. If you find that offensive, you can always go directly to the site in question and purchase direct.

    RibbonX - Customizing the Office 2007 Ribbon

    Naturally, I get to plug this one first, since it's mine! This is the definitive guide for customizing the Ribbon in Office 2007 (and higher):
    by Published on 2011-10-17 04:12 AM     Number of Views: 108348 

    I try all of the add-ins I recommend, and won't recommend them unless I believe they are worthwhile (particularly if there is a charge!) Each of those listed below are helpful tools that I have no problem recommending.

    In the interests of full disclosure here, any images below contains an affiliate link, so I do make a (small) amount of money if you click through and purchase. If you find that offensive, you can always go directly to the site in question and purchase direct.

    Chandoo's Excel Templates
    Chandoo is a fellow Excel MVP with a passion for dashboarding and Excel. He has Excel Templates for sale, as well as runs an online Excel School which includes Dashboard Training.

    Peltier Tech Charting Utilities for Excel
    Jon Peltier ...
    by Published on 2011-08-25 05:14 AM  Number of Views: 281695 

    ExcelGuru.ca is run by Ken Puls, FCPA, FCMA. I'm a Certified Management Accountant, who never remembers life without spreadsheets and computers.

    Since mid 1999, my career has been spent in the hospitality and tourism industry, in the finance and IT field. Starting as the Accounting Supervisor and Systems Administrator at Fairwinds Community & Resort on Vancouver Island, BC, I was promoted to Controller/Director of IT in September 2007. To this day, I dedicate a significant portion of my time to developing automated solutions and internal controls for the golf course, 400 berth marina, and two food and beverage outlets that make up the resort, as well as focusing time and energy on improving the Business Intelligence systems that run our real estate development. During this time, I've found that my true passion is designing and developing new systems, using such technology as Excel spreadsheets, databases built with Microsoft Access, and hand-held scanner technologies for taking inventory.

    Private Courses
    The tools within Microsoft Office are extremely powerful, and already exist on your systems... but do your staff know how to use them?

    The biggest issue facing corporate efficiency today is not the inability to afford powerful systems, but rather the lack of recognition of what you already have and how to leverage it. Does a knowledge gap exist in your staff's Office skill-set? Would you like to fix it?
    by Published on 2011-07-29 08:03 PM     Number of Views: 9310 
    Article Preview

    I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here’s how:
    by Published on 2011-07-15 08:35 PM     Number of Views: 8593 
    Article Preview

    If you’re running Outlook 2010 and Exchange 2010, you can set up your out of office replies any time during the day, and schedule them to take effect for a certain period. I wish I could do this with my phone… that way I could set it up when I think about it, and then forget about it. Currently I have to set a reminder to change my greeting just before I leave for the day… something that I’ve forgotten in the past due to the inevitable last minute chaos that always erupts just before you’re planning to go away for a week!
    The preview text goes here. If you don't enter anything here, the pagetext will be stripped of html and the beginning will be used.
    by Published on 2011-05-05 05:13 AM     Number of Views: 32896 

    Please Note: This text is the complete reproduction of Chapter 17 - Security if Microsoft Office from my book RibbonX: Customizing the Office 2007 Ribbon, and is reproduced with permission of Wiley Publishing who retains the copyright of this work.

    This chapter was obviously written for Excel 2007, and still holds true for Office 2010.

    In addition, this chapter also discusses macro security applicable to Office 97 through Office 2003. Key portions to read for these versions are Security Prior to Office 2007 and Digital Certificates. While some of the screens may look a little different, they should be close enough to help you understand how to set up a SelfCert digital certificate and add it to your projects. These steps will allow you to avoid macro warnings in your programming environment, without having to sacrifice security by setting your security levels to low. ...
    by Published on 2011-04-14 10:30 PM     Number of Views: 13939 

    In Introduction to the Excel WebApp, we learned how to create, upload and view files in Microsoft's Web Version of Excel, hosted on SkyDrive (a free offering from Windows Live.)

    It should be noted that thee main reason for creating a file in the Excel WebApp is to share it with others. So now that we’ve learned how to create and upload documents into our Windows Live SkyDrive account, let’s look at how to share them with our friends, co-workers and potentially anyone else in the world.

    Before we can share any documents using the Microsoft Excel WebApp though, we need to create a folder in SkyDrive for that purpose.

    Creating a Public Folder
    Log in to your Windows Live account, and navigate to your SkyDrive. Once you get there, create a new folder by clicking “New-->Folder”:

    In the screen that pops ...
    by Published on 2011-04-11 10:21 PM     Number of Views: 16813 

    In June 2010, Microsoft released the Excel WebApp as part of their Windows Live offerings. This is an interesting product as it allows you to store, read, edit and share your Excel document in the cloud. This means that it is no longer necessary to email your file around to all of your friends; you can have it stored in one place so that everyone is always working with the latest version of the spreadsheet. ...
    by Published on 2011-04-06 07:11 PM     Number of Views: 14816 
    Article Preview

    Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

    We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”
    by Published on 2011-03-10 08:43 PM     Number of Views: 13538 
    Article Preview

    In a discussion about PowerPivot yesterday, one of my friends stated that it wasn’t really useful since you couldn’t perform write-back using PowerPivot. To him this is a very important piece in the Excel budgeting process. Now, I agree that PowerPivot doesn’t give you write-back to a database, but this got me thinking; we have linked tables, so why couldn’t we create a write-back loop for a model that was built entirely in Excel? Well, we can!
    Page 2 of 6 FirstFirst 1 2 3 4 ... LastLast
  • MVP Logo
  • Recent Forum Posts


    Worksheet named from cell

    this is truly incredible! In an ideal worls I'd like to use data validation to populate the names that show in column A...

    AndyDuncombe Today, 08:58 PM Go to last post

    IF Formula

    I’m looking for help with a formula that will only do calculation based on if there is data in a cell. Example: ColumnA is an enrollment date but is...

    Jodie Today, 08:11 PM Go to last post
    Bob Phillips

    Worksheet named from cell

    I have made that change. I named the sheets as ddd d mmm as it takes less space, is just as clear, and means you see more roster sheets at one time. Each...

    Bob Phillips Today, 08:05 PM Go to last post

    Worksheet named from cell

    Which I'd like to rename as Sunday 2 Jan + sunday 9 Jan etc or whatever variant will fit. I think this would call from the app settings sheet data in...

    AndyDuncombe Today, 06:27 PM Go to last post
    Bob Phillips

    Worksheet named from cell

    The title does reflect that, do you mean the sheet name? Your example had sheet names WEEK 1 and WEEK 2....

    Bob Phillips Today, 03:30 PM Go to last post