Microsoft Excel Tips - Excel.Tips.Net Find a set of amounts that match a target value There are instances when one wants to figure out which elements of a set of numbers (amounts) total to a target value. One instance where this may be necessary is for a company that receives a check for outstanding accounts receivable (A/R) bills but doesn't have matching documentation indicating what bills are being paid. Excel template using Solver VBA code to find multiple matching combinations References For a shareware product that addresses the same issue, please visit TM Match Target. Recent Comments Excel template using Solver The Excel template uses Solver to provide one possible combination of numbers that total to the target amount. The Excel template VBA code to find multiple matching combinations There have been several requests for solutions that provide a list of all possible matches. Also, the code may take a fair amount of time to run depending on the size of the list of numbers being searched. How to use the code The second cell contains the target value. The code References

Cookies and Excel - Excel Liberation Option Explicit Private pHtmlHandle As Long Private pHtmlName As String Private pCookieName As String Private pDays As Long ' everything to do with creating cookies in excel Const cFailedtoGetHandle = -1 Public Property Get htmlName() As String htmlName = pHtmlName End Property Public Property Get cookieName() As String cookieName = pCookieName Public Function getCookie() As String ' with an initialized cookie, start up a browser and get it createCookieJar "get" getCookie = doCookie End Function Public Function putCookie(content As String) As String createCookieJar "put", content putCookie = doCookie Private Function doCookie() As String Dim cb As cBrowser Set cb = New cBrowser ' browse to it With cb .init .Navigate pHtmlName doCookie = .ElementText(pCookieName) .kill End With Set cb = Nothing Public Sub init(Optional hn As String = "cookiecjobject.html", _ Optional cn As String = "cookiecjobject", _ Optional ds As Long = 365) pHtmlName = hn pCookieName = cn pDays = ds End Sub ' create the script to get or put content

Excel Pages Excel Pages This page describes the various pages on the web site. This is the Page Index to the Excel Web Source site. The icon indicates that the content is related to Excel itself (typically the user interface) or to formulas. indicates that the content is related to VBA. indicates that the content is related to Visual Basic 6. indicates that the content is related to Visual Basic NET. indicates that the page has been converted from HTML to ASP.NET 2.0 indicates a download on the page. About This Site This page tells a bit about the site, along with acknowledgements, and miscellaneous information. Activating Excel From Other Programs If you are doing cross-application program (such as automating Excel from a Word document or template), you may find it useful for the user's convenience to activate Excel and set keyboard focus to the active sheet. Adding The COM Add-Ins Menu THis page desribes how to add the COM Add-Ins menu item to the Tools menu. Adding Menu Items To VBE Alert Message Component

Excel: Find closest value | Get Digital Help Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with a criterion Find closest value Question: How to find closest number in a list? Answer: Array formula in cell C2: How to create an array formula Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.Press and hold Ctrl + Shift.Press Enter onceRelease all keys Explaining array formula Step 1 - Return absolute values of numbers in an array, numbers without its sign ABS(number) returns the absolute value of a number, a number without its sign. becomes and returns Step 2 - Return the smallest number MIN(number1,[number2]) returns the smallest number in a set of values. returns 1. Step 3 - Return the relative position of an item in an array MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value returns 12. and returns 42. EDIT: Added excel workbook to this blog post Download excel example file

Custom Number Formats (Multiply & Divide by any Power of 10) Posted on January 31st, 2012 in Excel Howtos , Huis , Posts by Hui - 35 comments In the past here at Chandoo.org and at many many other sites, people have asked the question “How can I display a number Multiplied or Divided by 10, 100, 1000, 1000000 etc, but still have the cell maintain the original number for use in subsequent calculations“. Typically the answer has been limited to “It can’t be done” or “It can only be done in multiples of 1000”. Well thanks to a tip I picked up from Kyle who responded to a post here at Chandoo.org they are all wrong. It is possible to Multiply or Divide any cell contents by any power of 10 using Custom Number Formats ! That is: How does this work: When using custom number format we have two possibilities to modify the display number Use a Comma to divide by 1000; orUse a % to Multiply by 100 So using a combination of these any power of 10 can be obtained. So using the correct combination of , and % can result in any power of 10 multiplier we require. Downloads

Using Microsoft Excel Worksheet Functions in Visual Basic [Excel 2003 VBA Language Reference] The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location. Note Some worksheet functions aren't useful in Visual Basic. For example, the Concatenate function isn't needed because in Visual Basic you can use the & operator to join multiple text values. Calling a Worksheet Function from Visual Basic In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. Note Visual Basic functions don't use the WorksheetFunction qualifier. Example

Match two criteria and return multiple rows in excel | Get Digital Help Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates. If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date. Could you advice if there is any way to overcome? Answer: Here are two possible solutions: Create an excel 2007 tableUse an array formula The easiest is the excel 2007 table. Excel 2007 table Select the range Click "Insert" tabClick "Table" Click OK Click "black triangle" on Security header.Select one or more securitiesClick "black triangle" on Date header.Select one or more dates Array formula Array formula in F9: =INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER. How to create an array formula

Dead code detection and removal Increase quality, decrease files and save money by removing unused, dead code. Dead code – what's that? Dead code means unnecessary, inoperative code that can be – and should be – removed. The opposite of dead code is live, operational code. You can significantly improve a program's quality by removing dead code. If you're working with Visual Basic (or VB.NET or VBA), you can use our Project Analyzer to detect and even remove dead code. Types of dead code Dead procedure or function A dead procedure is not called by any other procedure. Dead produres make up the most important part of dead code. Many compilers include all procedures in the executables regardless of whether they are used or not. Even if you don't care a about the executable size, dead procedures have another adverse effect: they make your program harder to understand. If a dead procedure is later reused in the program, it may introduce new bugs in it. Dead variable A dead variable is not read nor written. Dead parameter

Nested Functions - Nested Functions in Excel A nested function in Excel refers to one or more function being placed inside another. This is done to extend the capabilities of a function. How it works is that the nested functions are used as arguments of the first function. Excel Nested IF Function Step by Step Tutorial Excel Nested IF Function Tutorial The usefulness of the IF function can be extended by using one or more Nested IF functions. Nested IF functions increase the number of possible outcomes that can be tested for and increase the number of actions that can be taken to deal with these outcomes. This step by step tutorial used nested IF functions to determine the deduction rate for employees based on their annual salary. Lookup Formula with Multiple Criteria in Excel Excel Nested Function Tutorial This tutorial nests the INDEX and MATCH functions to create a lookup formula that will use multiple criteria when finding information in a table of data. Excel Two Way Lookup Using VLOOKUP Excel MIN IF Array Formula Tutorial

Vertex42 - Excel Templates, Calendars, Calculators and Spreadsheets Excel and UDF Performance Stuff Different Ways to Take Advantage of the E-mail Features of Excel Ron de Bruin Microsoft Excel MVP Frank C. Rice Microsoft Corporation August 2003 Applies to: Microsoft® Excel 97 and later versions Summary: Microsoft Excel MVP Ron de Bruin provides a number of samples and a handy add-in that enhances your experience when working with e-mail from Excel. Contents Introduction This article features code samples, a wizard, and an add-in utility that you can use to perform various e-mail functions from Microsoft Excel. Note You should be aware of the two main security features for Outlook 2002 and later versions (and with a security patch for Outlook 2000): Blocking of a customizable list of file attachments considered unsafe because they can be used to propagate viruses. These constraints can affect the way you interact with Outlook in the following procedures. Sending E-mail in Excel One way to send e-mail from Excel is to the use the Mail Merge Wizard which is only available in Excel 2000 and later versions (called the Mail Merge Helper in Excel 2000). ... ...