Creating List and Retrieving Corresponding Field on Ms. Excel

This post is specially made for a girl.

You can download the samples about how to create list and retrieve field from corresponding item below on the same file and how to create list and retrieve field from corresponding item from different file below.

How to Create List in Microsoft Excel 2007

1. First of all, we have to make a list of items which are to be shown in the list as shown below:


Name the Worksheet: Items.

2. Create a new Worksheet on the same file, then go to a cell in which the list of items is to be prompted.

Image:

In the sample, the cell in which the items list is to be prompted is A4.

Click on cell A4, then go to menu Data -> Data Validation -> Choose Data Validation.


3. A dialog box will be prompted. On the Validation criteria, choose Allow: List.

Image:

4. On the Source field, fill the source of the items list previously made on the first Sheet, i.e. Sheet Items, then click OK. From the sample above, the source of the items are in the range A2 to A14.

Image:

Format of the source field:

=Sheetname!CellsRange

PS: $A$2:$A$12 -> $ is used on between the cells to fix the range; thus, when the formula on cells A4 is to be copied to the other cells, it will remain $A$2:$A$12 instead of changing according to the new cells.


5. Copy (ctrl+C) and paste (ctrl+V) the list already made on cell A4 to the other cells in which the items list is to be prompted. In the sample, cell A4 is copied to the cells below, i.e. A5..A27

Image:

How to Retrieve The Value of Corresponding field in Microsoft Excel 2007

As shown on the sample above:

Each item (on column A) has its corresponding price (on column B). After creating the list of items, we will retrieve the price of the item when an item is selected.

1. To make it more obvious, create a new Sheet on the same file again. Then, repeat the steps to make lists of items as shown above. Afterwards, add a column to the right to retrieve the corresponding price for the item selected. If everything goes fine, the result should look like the sample below.

Image:

2. On B4 (the cell in which the price is to be retrieved), insert the formula to retrieve the price of the corresponding item. In this formula, we use the functionality of INDEX to Return the value of a specified cell or array of cells and MATCH to Return the relative position of an item in an array that matches a specified value in a specified order (though there may be some other methods which can be used to do the same function).

Image:

Format of INDEX:

INDEX(array_or_list_of_items, row_number, column_number)

Format of MATCH:

INDEX(item_to_be_matched, lists_of_data, match_type)

We will not need match_type for the moment.
By the way, if you are interested in knowing further about the usage of the formula, refer to the help in the Microsoft Excel itself (press F1 on Microsoft Excel for help).


3. Copy (ctrl+C) and paste (ctrl+V) the list already made on cell B4 to the other cells in which the items list is to be prompted. In the sample, cell B4 is copied to the cells below, i.e. B5..B27

Image:

4. Try selecting the item, you will find out that the price of the corresponding item is shown according to the data on the Sheet Items once an item is selected.

Image:

WAIT! We are not done yet.
What if we want to make the list and retrieve the price from data (list of items and prices) which is located on another file?

1. Follow the steps above about how to create list. The only difference is on the formula.

Image:

Format of the formula if the data is located on another file on the same folder:

=[filename]Sheetname!CellsRange

Format of the formula if the data is located on another file NOT on the same folder:

='path\[filename]Sheetname'!CellsRange

e.g.

=INDEX('C:\[File1.xlsx]Items'!$A$2:$C$12)

2. Change the formula to retrieve the price too.

Image:

3. Copy and paste the cells to the cells below to ease your work.

Image:

Here are the samples: Data on the same file and Data on different file.