Excelguru.ca - Powered by vBulletin
  • Register
  • Help

  • Home
  • Products & Services
    • Live Training
    • Video Courses
    • Private Courses
    • Consulting & Mentoring
    • Training Calendar
    • Books
    • Affiliate Sales Program
    • About Us
    • Today's Posts
    • FAQ
    • Calendar
    • Community
      • Member List
    • Forum Actions
      • Mark Forums Read
    • Quick Links
      • View Site Leaders
  • Blog
  • Forum
    • Excelguru Forums
    • What's New?
    • Today's Posts
    • Forum Guide
    • Forum FAQ
    • Supported BB Code Tags
  • Resources
    • Online Training
    • Recommended Books
    • Add-ins & Tools
    • Excel Blogs
    • Useful Links
  • Knowledge Base
  • Contact Us
  • Advanced Search
  • Home
  • Home
  • Knowledge Base Articles

  1. If this is your first visit, then welcome! Be sure to check out the FAQ by clicking the link above.
    Register here so that you can post in the forums or comment on the articles.

  • Knowledge Base Articles By Category

    Access - ADO (4)
    Access - SQL (4)
    Access - VBA Programming (10)
    Downloads - Games (1)
    Downloads - Learning Aids (11)
    Downloads - Utilities (9)
    Excel - ADO (4)
    Excel - Charts and Data Visualization (1)
    Excel - Formulas (8)
    Excel - General Tips (36)
    Excel - Interactive Pages (9)
    Excel - PivotTables (12)
    Excel - Power Query (3)
    Excel - PowerPivot (7)
    Excel - SQL (4)
    Excel - UDF (7)
    Excel - User Interface Customization (2)
    Excel - VBA Programming (49)
    Excel - WebApp (4)
    Internet Explorer - VBA Programming (1)
    Novell Groupwise - VBA Programming (2)
    Other - General (1)
    Outlook - General Tips (5)
    Outlook - VBA Programming (7)
    PDF Creator - VBA Programming (11)
    Power BI (3)
    PowerPoint - VBA Programming (7)
    Publisher - VBA Programming (7)
    Syndicated Articles (7)
    Training Products (10)
    Word - VBA Programming (8)
  • Excel - Interactive Pages

    Retrieving Selections From A PivotTable Slicer 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-05-01 08:45 AM     Number of Views: 80697 
    Article Preview

    So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

    This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.
    ...
    Read More Read More 2 Comments

    Approximate Matches With VLOOKUP 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2012-03-22 07:00 AM     Number of Views: 30297 
    Article Preview

    The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.

    But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.
    ...
    Read More Read More

    Write-back Using PowerPivot 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2011-03-10 08:43 PM     Number of Views: 9723 
    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!
    ...
    Read More Read More

    Data Validation in Excel Services 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2011-03-08 09:09 PM     Number of Views: 16719 

    I’m a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.
    ...
    Read More Read More

    Another Excel Services (Skydrive) Example 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2011-03-03 09:35 PM     Number of Views: 9694 

    This particular file will show an interactive break-even analysis for an event entailing food and beverage.

    You can interact with the file and have the chart redraw. Probably the best route is to adjust by very large amounts to see the effects.

    Here you go, have a play!
    ...
    Read More Read More

    Understanding Dates in Excel 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2008-03-30 08:44 AM     Number of Views: 19999 

    What are dates?
    This may seem like a strange thing to ask but, as far as Excel is concerned, dates are numbers. By storing them as such, it gives us the ability to add or subtract days to/from a date, as well as get the difference between two dates. If dates were stored as text, this would not be possible. Storing dates as numbers also allows us to construct far more complicated formulas, based on results that we may want to know.
    ...
    Read More Read More 1 Comment

    Formula To Average Letters 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2006-01-17 10:53 AM     Number of Views: 28056 

    Formula Purpose:
    • This function takes a series of letters and returns the average of them, i.e., the average of A, B, C is B.
    ...
    Read More Read More 1 Comment

    Five Very Useful Functions For Working With Text 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2006-01-10 09:08 AM     Number of Views: 35437 

    This page is dedicated to explaining how to use what I believe are the five most valuable formulas for working with text in Excel. They are useful on their own many times, but can become immensely powerful when nested (combined) with other formulas later as well. Mastering these five formulas will open up the door to many things that you may have never thought possible. All of these formulas can be used by putting actual text in the "text" area, but their true power is unlocked when using them on cell references as the data can then be dynamic.
    ...
    Read More Read More 2 Comments

    Export A Range Of Excel Data To A Database 

    by
    Ken Puls
    • View Profile
    • View Forum Posts
    • View Blog Entries
    • Visit Homepage
    • View Articles
    Published on 2004-12-20 08:09 AM     Number of Views: 49661 

    Macro Purpose:
    • Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.

    ...
    Read More Read More 6 Comments
  • MVP Logo
  • Recent Forum Posts

    macropod

    workload allocation w/ skills matrix

    Cross-posted (and answered) at: https://chandoo.org/forum/threads/wo...-matrix.43173/
    Also cross-posted at:
    https://www.excelforum.com/excel-pro...ls-matrix.html...

    macropod Yesterday, 09:40 PM Go to last post
    Bob Phillips

    Remove duplicate cell value and not the actual cell.

    If you just don't want to see the value, you could use conditional formatting over A1:A11 with a formula of =COUNTIF($A$1:$A1,$A2) and set the font colour...

    Bob Phillips Yesterday, 05:22 PM Go to last post
    joyguru

    Remove duplicate cell value and not the actual cell.

    Thanks...

    joyguru Yesterday, 02:29 PM Go to last post
    Pecoflyer

    workload allocation w/ skills matrix

    Hi and welcome
    Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums. ...

    Pecoflyer Yesterday, 09:09 AM Go to last post
    shucky000

    workload allocation w/ skills matrix

    Hi,

    I'm currently creating a macro for allocation base on worker's skills matrix set.(determined by Y or N). So if the member is Y, worker...

    shucky000 2019-12-04, 10:22 PM Go to last post
  • Contact Us
  • Excelguru Forums
  • Archive
  • Top
All times are GMT +1. The time now is 09:43 PM.
Powered by vBulletin® Version 4.2.5
Copyright © 2019 vBulletin Solutions Inc. All rights reserved.
Pre-Defined Posts provided by Post Templates v1.6.1pl1 (Free) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
Thread / Post Bookmarks provided by Thread / Post Bookmarking v1.2.0 (Free) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
Copyright © 2004 - by Excelguru Consulting Inc.
Gravatar by 1e2.it