[Year 12 IT Apps] Building the B12 spreadsheet

[Mark Kelly]

But the exam sample had the customer's items listed...  that's what we 
were supposed to achieve.

Litsa Tzelepis wrote:
> 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.
>> _______________________________________________


-- 
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.