On the StockList sheet, you need to include the ISBN Numbers of the books listed on the BookList sheet. Creating simple links as on the previous page may at first glance seem adequate, but it is important that the reference between the book title and its ISBN number is never changed. Therefore, it is necessary to enter a function into the spreadsheet which says, "Find this book title and give me its ISBN number". This is done using the VLookup function.
To make VLookUp work correctly, it is important to define a named range of cells containing the data to be looked up.
Select
the BookList sheet and highlight the cells containing all the book titles and
ISBN numbers. Now select Insert from the menu, move across Name
and select Define...; in the Define Name dialogue box which appears,
enter the name title_isbn or similar (single words only, please!), checking
that the correct cell references have been referred to at the bottom of the
box. Click on OK then save the file. Note that any block of cells can
be named in this way and referred to by the defined name.
Now
return to the StockList sheet and select the cell to the right of the first
book title. Press the = key (or Select the = icon) to enter the
formula mode, then select the function pull-down menu which is to the left and
select VLookUp from the list. (If VLookUp is not in the list,
Select More functions... and locate it under the category Lookup &
Reference.) Whichever method you chose, a dialogue box should appear on
the screen into which you need to enter the details which the function needs
to work.
Take a deep breath
before starting this section! The VLookUp function needs a minimum of
three pieces of information for it to work correctly. The first of these is
the information you want it to find (or match); in this case, this is the book
title which is in the cell to the left of the current cell. To refer to this
cell, select the cell chooser icon ()
and then Select the cell with the mouse pointer; Select the cell chooser
icon again or press Enter on the keyboard to return to the dialogue box
(check that the correct cell has been entered - if it hasn't, repeat the process).
The second piece of information you need to enter, is the range of cells containing
the data to be matched - this will be the block of cells you named title_isbn
(or similar) earlier. To enter this, either enter title_isbn (or
whatever you called the block of cells), or Select the cell chooser icon
and highlight the cells containing all the book titles and ISBN numbers (press
Enter or Select the cell chooser icon to return to the dialogue box). The third
piece of information is to enter the number of columns to the right of the Title
column (this is counted as column 1) you want data returned; in this case the
next column on the right, so enter 2. Optionally, you can include a fourth piece
of information, which is to choose whether Excel makes an exact match or a close
match. I recommend you enter False here (which, strangely, means an exact match!).
The image below shows the VLookUp dialogue box with the information entered
and the cell chooser icons highlighted.
If it helps,
click the image above to see another, step-by-step explanation.
To check everything works correctly, resort the BookList data and ensure that the StockList data has mirrored the changes. If all is well, save the file.
Previous>Stocklist... |