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 20071. 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.
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.
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.
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..A27How to Retrieve The Value of Corresponding field in Microsoft Excel 2007Each
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.
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).
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..B274. 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.
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.
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.
3. Copy and paste the cells to the cells below to ease your work.
Here are the samples:
Data on the same file and
Data on different file.
