Excel f(x)s = Excel Functions
Lookup and references functions, comes with Excel
This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.
Formulas used to bring actual data is:
=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))
While formula used to bring header labels:
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)
Enter a zip code, and Excel will tell you in which state it is
It also has a list of all zip codes for all states and a technique (INDEX and MATCH functions) to get the state from a given zip code.
Simple file to help learning INDEX and MATCH
The formula used is: =INDEX(Data!$D:$D,MATCH(TEXT(Search!C4,"00000"),Data!$C:$C,0),1)
Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table
Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above
Which is usually what you got from any list of addresses online, and you want to convert it to table.
This is exactly what one of clients had and needed, so here is the file that does that.
Basically you need to have 5 columns with formula in column G as:
=OFFSET($B$1,(ROW()-5)*4+4,0)
to extract first line (Starting from cell B5
Then in column H as:
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:
Once you open the sheet and enable macros, you can use function:
=WorksheetsNo()
To get total number of sheets in that workbook
And use function:
=WorksheetName(1)
To get sheet name for first sheet, and
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…
