Newsletter - Apr. '08
|
|
Spreadsheet Tips
|
|
In this month's tips we return to the Vlookup function
which cross-references data. We look at combining data from different
files in the context of a management task, preparing data for presentation.
|
|
|
Spreadsheet Tips - Vlookup across files
|
Clients contacting Working Data to help them compile and compare data,
often have their data spread over different workbooks, or files.
This can occur when members of staff keep separate spreadsheets.
The data in these spreadsheets can be cross-referenced, however,
with the Vlookup function, as we will see in the context of a management task;
Preparing data for a presentation.
|
|
|
Preparing data for a presentation
|
|
|
At Blooming Data, our flower shop, the accountant has returned our year-end figures.
One of the spreadsheets shows the quantity of each flower we have sold in the
last year. The manager is preparing a presentation showing how sales
of each product have changed since the previous year.
However, last year's figures are in a different spreadsheet.
We can use the Vlookup function to bring all this data together into
one manageable table. |
|
| Fig 1 |
 |
|
|
Fig 1 shows the data from 2007. We want to pull through the figures for
2006, but in the 2006 spreadsheet (Fig 2) the products are in a different order, so we can not simply copy and paste.
We could manually go through the list and type the figures in one at a time.
Alternatively, we can let the Vlookup function do all that work for us
and give us our presentation figures instantly.
|
|
| Fig 2 |
 |
|
|
In the 2007 spreadsheet, we write a heading into the column to the left of the 2007 figures, called 2006. See Fig 3. |
|
| Fig 3 |
 |
|
We then enter the Vlookup formula into cell B7 which we will copy down the rest of the column.
Remember from our December '07 newsletter that the
Vlookup function has four elements, or "arguments".
- The value to be looked up. In this example it is A7 (Rose).
- The table we want to look it up in, here it is cells
A7 to B13 in the "sales_product" tab of the spreadsheet
called "2006.xls" in "C:" (see Fig 2). We put dollar signs ($) in front of each part of the cell address to make sure that the cells we are looking up don't change as we copy the formula down the column in a later stage. e.g. $A$7:$B$13.
- The column number of the value that we want the
formula to return.
In this instance it is 2. Looking at Fig 2, the 2006 spreadsheet, we see that column 1 is the name of the flowers, and column 2 is the quantity sold.
- This is a TRUE or FALSE option. We select FALSE as this forces
Excel to find an exact match for the flower name.
In cell B7, we enter
=VLOOKUP(A7,'C:\[2006.xls]sales_product'!$A$7:$B$13,2,FALSE)
This will pull through the number sold in 2006 for the flower in cell A7, Rose.
See Fig 4.
|
|
| Fig 4 |
 |
|
| 510 Roses were sold in 2006. You can check this by looking at the data in Fig 2. We now copy the formula down the rest of the column. The figures for 2006 are pulled
through and allow easy comparison with the 2007 figures. You can now add a formula
which will show percentage change between 2006 and 2007 in cell D7 and copy this
down the column to show the difference, as in Fig 5. These figures are now ready
for presentation and will guide buying patterns for the year ahead. |
|
| Fig 5 |
 |
|
|
|
You could combine this with a spreadsheet of costs and prices to show which
flowers are giving you the greatest margins and bringing you the most profit.
|
|
|
If you have any questions about the Vlookup function and when to use it,
or if you would like to suggest a topic for us to cover in a future
newsletter, please contact us.
|
|
|
back to top
|
|
|
|
|
