Excel f(x)s = Excel Functions
Two or more functions = One Formula, how to combine more than one function to present a powerful formula that does the job well
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:
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)” »
Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas
Can also be used as form to read from a large table in another sheet
After creating two names, one is “Months” having:
=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1)
And the other one is “Month_All” having:
=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3)
Draw a listbox using “Developer toolbar”, then make its input list as “Months” name we did above, then use these formulas:
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
Rotating table (Transpose) using functions so that the table is updated once the source table is updated. Also can be used as automatically rotating tables with variable number of rows and columns Sheet name can be used inside formula so that you can have multiple tables from different sheets to be rotate it into one…
Sorting a list automatically using formulas, with no need to press the sort command Also if the source table is changed, the destination table will do also. What you need is basically two formulas, one for the sort-by column to list items by order. Use SMALL to sort ascending, or LARGE to sort descending, then…
Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…
An Excel file that will determine what cash to return to the buyer (How much of each cash unit, or how many of Fifty dollars and how many of Twenty dollars and so on) in a very interesting way using as less functions as needed So, if you need to return 15.45, this file will…
Read More “Change Calculator – calculate change in multiple bills/coins” »
This one is very similar to Tally chart, however, it is for another purpose. It is used to draw a progress bar, yes a progress bar in pure functions, using the formula: =REPT( CHAR( 8),INT( D2/10))&REPT( CHAR( 7),INT( D3/10)-INT( D2/10))
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 >…
