Fully customized Yearly Calendar to show certain dates with certain format using conditional formatting, like mark 8th business day each month with certain color, 1st workday after 15th of each month, etc.
See the sheet “Data” for details and table of specific dates and rules. Contact me for more detail
Category: Data Validation
Skills Grid
A small Excel file to show how we can create a chart-like Excel sheet
Used in my Resume to show different skill sets and the level of expertise in each
State Abbr with Index+Match+Validation
Small file that will show how to do INDEX + MATCH formulas to get the abbreviation of the state based on its name or the name of the state based on its abbreviation
Main formula to do that is:
=INDEX(Abbr_States,MATCH(B4,States,0),1)
Multiple Dual-Validations
It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it
Mutliple-dual-validations
that depend on each other.
Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the main that used in column B in that row.
Creating something like this basically falls in three parts
Part1: is the Names you need to define, so after we create the table found “Data” sheet …..
We need to set two names, one for the left-top cell for that table (used as reference) and another name with formula:
Multiple-Validations (connected to each other)
Here is the free file for a special request by a client
He wanted to have multiple cells having Data – Validation to same list but minus the selected one
In English, once you select one from cell1, cell2 will bring the same list but without the selected one, then when you select another item is selected in cell2, cell3 will bring what left from the list.
Does that make sense, check out the screenshots
Read More “Multiple-Validations (connected to each other)” »
Validation based on another Validation
Creating a Data Validation based on another Data Validation
Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it.
You need to create two names (Formula > Name Manager), one has this formula
=OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2))
The other one has this
Excel Dynamic Drop-Downs that grow with you
This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox. The screenshot almost says it all. You need 4 steps: 1- Create the name for a column in your Excel file, go to Names > Define in Excel2003 or earlier, or go to Formulas >…