Nick, You could start by creating the list '#' and then create another list of the 'in use' numbers then simply loop through the 'in use' checking if the current value is within the '#' list and consequently doing a lookup to add the value.
The code below should get you started. <cfquery name=get> SELECT # as references, inuse as answers from "sheetname$" </cfquery> <cfset reflist = valuelist(get.references)> <cfset answerlist = valuelist(get.answers)> <!--- set the total variable ---> <cfset total = 0> <cfloop list=answerlist index=this> <cfif listfindnocase(reflist, this)> <cfquery name=score> SELECT $ FROM "sheetname$" WHERE # = #this# </cfquery> <!--- add the score to the total ---> <cfset total = total + score.$> </cfif> </cfloop> HTH Dave -----Original Message----- From: Douglas Brown [mailto:[EMAIL PROTECTED]] Sent: 19 April 2002 16:59 To: CF-Talk Subject: Re: WOT: MS Excel! Help me or shot me! Why not just use the compare function of CF instead of using excel? Then using <cfscript> do a loop through it if the value eq 0 add the colums etc. etc..I hope I am making sense !!! Doug Brown ----- Original Message ----- From: "Nick McClure" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, April 19, 2002 7:13 AM Subject: Re: WOT: MS Excel! Help me or shot me! > Can't help with Excel part, but I can shoot you :) > > At 09:14 AM 4/19/2002 -0500, you wrote: > >I have one of the VP's here needing some Excel help and I have been > >given the job of doing it. > > > >He wants to check a group of cells on the right (in use) and see if they > >match any of the numbers on the left (#). > >Then add the value of the cell next to the matching numbers ($). I tried > >the SumIf function but it didn't work. > > > >Some times it doesn't pay to be computer literate. On second thought, > >just shot me. > > > > > ># $ In use > >-------------------------------------------------------------- > >1 22 1 3 5 > >2 11 6 9 > >3 33 > >4 44 > >5 22 > >6 32 > >7 56 > >8 12 > >9 45 > > > > > >Total > >154 > > > > > >Phillip Broussard > >Tracker Marine Group > >417-873-5957 > > > > > > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
