[Year 12 IT Apps] Building the B12 spreadsheet

[Litsa Tzelepis]

maybe we don't need to itemise the "delivery" slip.
maybe it needs just the address, etc.


----- Original Message ----- 
From: "Mark Kelly" <kel at mckinnonsc.vic.edu.au>
To: "Year 12 IT Applications Teachers' Mailing List" 
<itapps at edulists.com.au>
Sent: Thursday, November 12, 2009 12:30 PM
Subject: [Year 12 IT Apps] Building the B12 spreadsheet


> 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.
> _______________________________________________
> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html - 
> Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers 
> Association Inc