[Year 12 IT Apps] Building the B12 spreadsheet

[Mark Kelly]

Hi all.  I'm bamboozled.  I'm trying to construct the spreadsheet 
described in ITA exam B12.  My attempt so far is attached.

The stumbling block is the deliveryslip sheet. It's easy to populate the 
customer fields with a series of VLOOKUPS.

The question is how does the sheet pull the nominated customer's items 
from the deliverycosts sheet, and the corresponding weights?

The only way I can see is to put a stack of statements like:
=IF(id=deliverycost!A7,deliverycost!B7,"")

(Where 'id' is the name of the cell containing the customer ID on the 
deliveryslip sheet.)

So, if the customer ID on the matching row on the deliverycost sheet 
matches the ID on the deliveryslip, it returns the item name from the 
deliverycost sheet.  Same routine for the item weights.

But, of course, for any item that is NOT bought by the selected customer 
there is a blank line!  Hardly satisfactory.

And, of course, this only produces one customer at a time.  It can't 
produce output "for each customer" as the question demands.

So is there anything like a multiple VLOOKUP that finds *all* matching 
values in another table and returns them in an array?

I've been playing with Excel for some time now, and I've never heard of 
such a thing.  I wonder what the kids suggested...

Over to you.

-- 
Mark Kelly
Manager - Information Systems
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: 8520 9085
School Phone +613 8520 9000
School Fax +613 9578 9253
kel at mckinnonsc.vic.edu.au

Webmaster - http://www.mckinnonsc.vic.edu.au
IT Lecture notes: http://vceit.com
Moderator: IT Applications Mailing List

RealMenDon'tNeedSpacebars.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: B12 prototype.xls
Type: application/msexcel
Size: 26624 bytes
Desc: not available
Url : http://www.edulists.com.au/pipermail/itapps/attachments/20091112/88c2b463/B12prototype-0001.bin