Excel f(x)s = Excel Functions
This Excel file will convert a table of two columns into a Column-Row table
Using functions only and auto-updated once the Main table update
In another word, analyze the table into wider form
I needed this one few days ago and you will need this one too.
The main formula that you need to use is:
It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it
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:
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
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 >…
