Author: ANmar

Extract Address into table

February 10, 2012 ANmar No Comments

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:

Formulas - combined functions, LEFT, Lookup and References, MID, OFFSET, ROW, SEARCH, Standard functions, Texts and Strings

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

February 9, 2012 ANmar No Comments

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:

Formulas - combined functions, IF, Lookup and References, MATCH, OFFSET, ROW, Standard functions

Multiple Dual-Validations

February 9, 2012 ANmar No Comments

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:

Read More “Multiple Dual-Validations” »

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, Names, OFFSET, Standard functions

Multiple-Validations (connected to each other)

February 7, 2012 ANmar No Comments

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

Data Validation, Formulas - combined functions, IF, Logical, Standard functions

WorksheetName and WorksheetsNo UDFs

February 7, 2012 ANmar No Comments

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

Lookup and References, ROW, User-Defined f(x)s = UDF, Worksheet

Dynamic selection list

February 7, 2012 ANmar No Comments

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:

Read More “Dynamic selection list” »

ActiveX controls, COUNTA, Formulas - combined functions, INDEX, Lookup and References, Names, Standard functions

Validation based on another Validation

February 7, 2012 ANmar No Comments

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

COUNTA, Data Validation, Formulas - combined functions, Lookup and References, MATCH, OFFSET, Standard functions

Rotate table using 1 formula

February 6, 2012 ANmar No Comments

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…

ADDRESS, COLUMN, Formulas - combined functions, INDIRECT, Lookup and References, ROW, Standard functions

Sort list dynamically (functions)

February 5, 2012 ANmar No Comments

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…

COLUMN, Formulas - combined functions, INDEX, LARGE, Lookup and References, MATCH, Math and Trig, ROW, SMALL, Standard functions

Insert Blank rows into table using functions

February 5, 2012 ANmar No Comments

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…

CHAR, COLUMN, Formulas - combined functions, IF, INDIRECT, INT, Lookup and References, ROW, Standard functions

Change Calculator – calculate change in multiple bills/coins

February 1, 2012 ANmar No Comments

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…

Formulas - combined functions, IF, INT, Logical, Math and Trig, Standard functions, SUMPRODUCT