<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">I love it when people talk hard-core Excel!<div><br></div><div>Simon</div><div><br><div apple-content-edited="true"> <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>---</div><div>Simon Robinson</div><div>eLearning</div><div>St Columba's College</div><div><a href="http://yourdigitalconsultant.edublogs.org">http://yourdigitalconsultant.edublogs.org</a></div></div></span></div></span> </div><br><div><div>On 01/08/2008, at 12:10 AM, <a href="mailto:maced-request@zeus.as.edu.au">maced-request@zeus.as.edu.au</a> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; ">Date: Fri, 1 Aug 2008 00:12:43 +1000<br>From: Adam Rice <<a href="mailto:arice@mac1.com.au">arice@mac1.com.au</a>><br>Subject: Re: [Maced] Carnival Manager<br>To: Macs in Education <<a href="mailto:maced@zeus.as.edu.au">maced@zeus.as.edu.au</a>><br>Message-ID: <<a href="mailto:317A24ED-1DFC-44A6-BB32-BF0171499897@mac1.com.au">317A24ED-1DFC-44A6-BB32-BF0171499897@mac1.com.au</a>><br>Content-Type: text/plain; charset="windows-1252"<br><br>I hate to disagree with you but Excel is a VERY powerful application <br>and this would be very easy to do.<br><br>You would create it in a very similar way to how the FileMaker app is <br>done in that you break it out into separate sheets for ease of use.<br><br>I actually had a big example here except I realised it was too <br>difficult to read in an email and no one would be able to digest it so <br>instead I'll give a summary of my own real world use and hope you can <br>trust that I had a complete solution written out.<br><br>I used to have to forecast to Apple the number of iPods my previous <br>employer would require each week broken down by store.<br>The retailers would sent me CSV dumps of their stock out of their <br>system in a range of ways and it was pretty much impossible to get <br>them to give you the data in the required format.<br><br>First task was to use the vertical lookup (VLOOKUP) function of excel <br>to pull out the data I needed in the cells I needed it without <br>spending any time messing with the document.<br><br>Next step was to insert what we had sold the customers into the <br>document which I got out of our system as a large CSV. A simple <br>VLOOKUP wouldn't work here because the same product code would show up <br>in different customers and a VLOOKUP finds the first occurrence <br>(RELATIONSHIP - pulling data from multiple events into 1 points page) <br>so I would use multiple VLOOKUPs in one cell to firstly find the <br>customer and then find the product IF it existed and do nothing if it <br>didn't.<br><br>Then I would need to add the stock data in except stock could be in <br>multiple states and it needed to be grouped by state giving me a <br>summary of number of items per product code per state (RELATIONSHIP - <br>what if there is a tie). This is done with a pivot table which takes a <br>large set of date that is not sorted and organises it into a <br>summarised list for you. Then a simple VLOOKUP would pull the data to <br>where I needed it (notice the reoccurring command here)<br><br>To skip forward to after we had received the iPods I requested for the <br>week, I would then need to share out the stock amongst the customers <br>but there were a range of factors in this such as whether there was <br>enough stock to cover, was the customer on credit hold, did they have <br>a catalogue, where there any other priority shipping agreements <br>(RELATIONSHIP - multiple points systems). Simply having a variables <br>set for each customer such as "Credit Hold: No", "Mass Merchant: Yes" <br>would tell the allocation procedure how to handle the stock and how <br>much to give to the customer. For something like a points system, you <br>would have a sheet that has the points listed out and set some kind of <br>variable for it the same way that FileMaker package does.<br><br>Collating results is just reporting. The same way the FileMaker system <br>had to have someone program in report styles, you would just a heap of <br>different reports set and could either find them by sheet or use a <br>simple hyperlink to the location of the report.<br><br>Slightly off-topic, VLOOKUP is one of the best features of Excel and <br>takes it from basic data entry to a statistics management device.<br><br>Adam Rice<br>K-12 Education Manager - Brisbane & Sunshine Coast<br></span></blockquote></div><br></div></body></html>