iframe in Excel (XLiFrame)

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:

Zipcode-State search

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)

Extract Address into table

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, […]

Convert 2-column into wide (column-row) table

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 […]

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 […]