now browsing by author
There are 3 Reference Operators in Excel:
- “:” (colon) is the Range operator, which produces one reference to all the cells between two cells references, including the cells written in the reference text, like: A11:A13
- “,” (comma) is the Union operator, which combines multiple Ranges into one reference, like A11:A13 , A11:C11 , A11 (you can refer to simple cells too, not only to ranges with multiple cells)
- ” ” (space) is the Intersection operator, which returns a reference to the cell or to the range of cells found at the intersection of the ranges. In this example, only cell A11 is found in both ranges: A11:A13 A11:C11
How can the Cell Modes affect your work?
When you try to edit a Conditional Formatting formula, and you click the left or right arrows to move across formula’s text, Excel inserts unwanted cell references. Same thing happens when you try to create or edit a Defined Name formula, or a Chart series…
If you are not aware of the active Cell Mode, then working with formulas, defined names formulas or conditional formatting formulas, editing chart series can become very annoying.
If you’re looking for a Client Relationship Management tool,
you can try the Excel CRM. The lite version has 3 modules active: Contacts, Organizations and Calendar, these are the most popular.
Download Excel CRM: Excel CRM lite.xslm
The full version has a lot more modules, as you will see on the opening page. Each module has it’s corresponding User Form:
Contacts User Form:
This article describes how to create mass letters from Excel, using a Microsoft Word Document as the letter template and a Map between Excel Defined Names and Word Document Bookmarks.
Starting from the example described in this article, there are numerous things that can be done: if we setup the same bookmarks in Multiple Word Documents, and if we use a multi-select dialog box to open and fill all those selected documents, we can create A SET of different documents for each recipient! Of course, all letters can be attached to an email and sent to that recipient email.
Most of the times,
It should be obvious you first need to create an Excel Table containing the name, physical address, email address, and other pertinent information that is to be inserted into the letter/email of all your intended recipients.
To create a letter for all recipients from your table, a Mail Merge will be all you need. But there are many scenarios where you need flexibility:
- you may want to create a letter for only 1 recipient, without going through all Mail Merge steps;
- or you need to select a range of rows and create letters for all selected rows,
- or, you may want to filter the table for different criteria, and create letters just for visible rows,
- Create Letter for each row in table.
The following will cover all these scenarios, the letters will be saved as PDF files. Read More
Add New Controls to Excel UserForm
In this article, you will find code for adding new controls to an Excel User Fom, for adding items to comboboxes and to create an array of values from a range of cells.
Excel does not provide a function for linear interpolations. If your data table has a low granularity (you have only units, not sub – units), and you need precise results, you have to create your own linear interpolation formula. You will find in this article an excel formula, and a User Defined Function (UDF) for Linear Interpolation in Excel. Read More
Here is a ScoreBoard View:
This one is dedicated to all Table Tennis fans 🙂 , hope they will find this Excel Table Tennis ScoreBoard useful.
I’ve built this application for our table tennis team, High-Tech, you will find us in the list of teams and players 🙂
The Excel Table Tennis ScoreBoard will work in Excel 2007, 2010 and excel 2013, on 32-bit and 64-bit versions, not tested in Excel 2003. (forget about this version, there are many new excellent features in the newer versions) Read More
Autoexpand Excel Tables on Protected Sheets
An important feature of Excel Defined Tables, the autoexpanding functionality, is lost when the worksheet is protected. On a protected worksheet, when you type anything under the last row, the table will not automatically expand to include the new row, as you might expect. The decision to protect the sheet is not easy when you need both protection and autoexpansion capabilities for an Excel Table.
Most likely, Microsoft engineers are well aware of this limitation, but until they come up with a solution to this problem, you can try my solution. The solution involves a little Visual Basic programming, but fortunately for you, I have taken care of the programming part.
Using the Worksheet_SelectionChange event,
we can detect what cell is selected by a user, and then we can decide if we need to unprotect (or not) the worksheet. Read More
If your workbook contains macros designed to assist you or your users in typing data, or you have UDF’s – User Defined Functions for specific calculations, then if the users do not enable macros after opening the file, your codes are useless…