Creating a Stock List

The next sheet will hold details of the numbers of books currently held in stock. Although it looks like a simple sheet, there are a number of steps to go through to make certain that errors are kept to a minimum.

To start, name the sheet label StockList (refer to the first page if you can't remember how to do this). Now enter the headings Title, ISBN No, Original Stock and Current Stock. Rather than manually enter the details of the books from the BookList sheet, we will create links so that any changes to the BookList will automatically update the Stocklist sheet.

To enter the book titles, select the first cell below the Title heading and either press the = sign on the keyboard or select the = icon to the left of the formula bar to begin entering a formula (or link in this case). Whilst in this formula mode, any cell selected will be added to the formula so be careful where you click next. You need to create a link to the first book title in the BookList sheet so carefully select the BookList label and click on the cell containing the first book. To finish, either press the enter key on the keyboard or click on the OK button in the formula box. The first book title should now be displayed in the StockList sheet.

To enter the remaining book titles, you should use the Fill Down function. There are two main ways of filling down in Excel, one being a quick way the other being slightly more involved:
Quick way: select the cell containing the first book title, place the pointer on the small square at the bottom, right corner of the cell and drag down over the next five cells.
Involved way: highlight the cells containing the the first book title and the five empty cells below (drag across them). Select the Edit Menu and move across Fill then select Down.

Any changes to the book titles on the BookList sheet will automatically change the titles in the StockList sheet. Try it! Now add details of three new books to the BookList, sort the data and update the links on the StockList sheet. Under the Original Stock heading, enter 50 in each cell: this will be the starting stock level of each title. Save the file.