One of the items left to fill on the invoice, is a check as to whether or not the book is in stock. The cells under the In Stock? heading will show two states of stock: Yes (there are stocks of the book) and No (out of stock).
If
you look at your StockList sheet, it should look similar to this image
(assuming you have deducted the quantity of books ordered from the invoice!).
The cells showing #N/A are there to show that there is no data on which the
function can work, i.e. none of these titles have been ordered. It is on Current
Stock cell values that we are going to base our check.
On the Invoice worksheet, select the first cell below In Stock?, Select the = sign (or enter = from the keyboard) and choose IF from the function selector to display its dialogue box. If you think logically about this, what we are asking the IF function to do is to look at the book title in the invoice, find the same title in the StockList worksheet and return Yes if Current Stock is greater than 1 and No if Current Stock is less than 1. This means that we will have to embed a VLookUp function inside the IF function!
To
do this, in the IF function dialogue box, enter the return values for
Value_if_true and Value_if_false first, then place the text cursor
in the Logical_Test section and select VLookUp from the function
selector to display its dialogue box. Enter the parameters to check the stock
level for the title to be matched and Select OK. At this point, Excel will probably
throw up an error message, as we haven't actually completed the function. In
the formula bar, place the text cursor immediately to the right of the closing
bracket of the VLookUp function and enter <1 (less than 1)
and either press the Enter key or Select the tick mark to finish
the function. The completed function should appear as:
=IF(VLOOKUP(A11,StockList!$A$1:$D$7,4,FALSE)<1,"NO","YES")
Note the absolute cell referencing to the data held on the StockList worksheet. This means that when the function is copied down, the StockList references do not change. You could, of course, change the function by making it check for StockList >1 (greater than one) and then reverse the Value_if_true and Value_if_false parameters.
| Previous>Invoice... |