I posted this over in the Excel forum yesterday and received no responses (but 29 views). I'm thinking maybe this forum is more appropriate.
I am VERY new to Access, and have a spreadsheet that is generated every day that I need to selectively export data from to an Access database. My staff creates a new spreadsheet/workbook every day (which are named "072314" for today, "072414" for tomorrow, etc) and I download these from the office computer every night. On each row of the spreadsheet, a customer record is created, and some customers end up having a balance due which I need to create a database for to keep track of. Additionally, there are two (actually three, but on the sample sheet that I am sharing, I only entered two) different types of balance due that I have to keep separate so that my staff can then work with it daily in a separate database and update accordingly as the customer makes payments. I would like to add each day's balance dues to one continuously growing database.
What would be the best and easiest way to do this? I have tried some tutorials on how to export data to Access, but none of them really fit what I need to do. Plus, as I said, I am very new to Access as the last 20 years I have worked almost exclusively with Excel. (I'm not certain if I 'attached' the worksheet properly below using HTMLMaker. This is the first time I've used it.) The spreadsheets contain a lot of <acronym title="visual basic for applications">VBA</acronym> code to expand column widths when a cell is selected for my staff to read codes and long descriptions in a drop down list, so I don't want to 'break' those when scraping data to the database.
I am VERY new to Access, and have a spreadsheet that is generated every day that I need to selectively export data from to an Access database. My staff creates a new spreadsheet/workbook every day (which are named "072314" for today, "072414" for tomorrow, etc) and I download these from the office computer every night. On each row of the spreadsheet, a customer record is created, and some customers end up having a balance due which I need to create a database for to keep track of. Additionally, there are two (actually three, but on the sample sheet that I am sharing, I only entered two) different types of balance due that I have to keep separate so that my staff can then work with it daily in a separate database and update accordingly as the customer makes payments. I would like to add each day's balance dues to one continuously growing database.
What would be the best and easiest way to do this? I have tried some tutorials on how to export data to Access, but none of them really fit what I need to do. Plus, as I said, I am very new to Access as the last 20 years I have worked almost exclusively with Excel. (I'm not certain if I 'attached' the worksheet properly below using HTMLMaker. This is the first time I've used it.) The spreadsheets contain a lot of <acronym title="visual basic for applications">VBA</acronym> code to expand column widths when a cell is selected for my staff to read codes and long descriptions in a drop down list, so I don't want to 'break' those when scraping data to the database.
HTML:
Excel 2002ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE12345Balance DueBalance Due6Bill #Last NameFirst NameMIBirthdayProc CodeProc CodeProc CodeProc CodeDiagnosisDiagnosisChargeSpcl ChargeType # 1Type # 278901SmithBob5050150501 360.08360.04 $ 200.00 - 120.00 20.00 - - 001002SmithMary50503360.01 $ 20.00 - - 1103JonesJim5050650505360.03 $ 30.00 - 10.00 - 1204MarksCindy50508360.05 $ 20.00 - 10.00 - 1305BelliniMartin5050150504360.07 $ 100.00 - - 1406GrantFrances50503360.02 $ 20.00 - - 1507 $ - - - 1608 $ - - - 1709 $ - - - 1810 $ - - - 1911 $ - - - 2012 $ - - - 2113 $ - - - 2214 $ - - - 2315 $ - - - 2416 $ - - - 2517 $ - - - 2618 $ - - - 2719 $ - - - 2820 $ - - - 2921 $ - - - 3022 $ - - - 3123 $ - - - 3224 $ - - - 3325 $ - - - 3426 $ - - - 3527 $ - - - 3628 $ - - 2 - 373839[Reserved for Future Use]4041424344454647[CENTER][COLOR=#161120][B]Daysheet[/B][/COLOR][/CENTER] Worksheet FormulasCellFormulaA9=CONCATENATE($C$1,0,1)B9=IF(U9+V9+W9=S9,"","X")A10=CONCATENATE($C$1,0,2)B10=IF(U10+V10+W10=S10,"","X")A11=CONCATENATE($C$1,0,3)B11=IF(U11+V11+W11=S11,"","X")A12=CONCATENATE($C$1,0,4)B12=IF(U12+V12+W12=S12,"","X")A13=CONCATENATE($C$1,0,5)B13=IF(U13+V13+W13=S13,"","X")A14=CONCATENATE($C$1,0,6)B14=IF(U14+V14+W14=S14,"","X")A15=CONCATENATE($C$1,0,7)B15=IF(U15+V15+W15=S15,"","X")A16=CONCATENATE($C$1,0,8)B16=IF(U16+V16+W16=S16,"","X")A17=CONCATENATE($C$1,0,9)B17=IF(U17+V17+W17=S17,"","X")A18=CONCATENATE($C$1,1,0)B18=IF(U18+V18+W18=S18,"","X")A19=CONCATENATE($C$1,1,1)B19=IF(U19+V19+W19=S19,"","X")A20=CONCATENATE($C$1,1,2)B20=IF(U20+V20+W20=S20,"","X")A21=CONCATENATE($C$1,1,3)B21=IF(U21+V21+W21=S21,"","X")A22=CONCATENATE($C$1,1,4)B22=IF(U22+V22+W22=S22,"","X")A23=CONCATENATE($C$1,1,5)B23=IF(U23+V23+W23=S23,"","X")A24=CONCATENATE($C$1,1,6)B24=IF(U24+V24+W24=S24,"","X")A25=CONCATENATE($C$1,1,7)B25=IF(U25+V25+W25=S25,"","X")A26=CONCATENATE($C$1,1,8)B26=IF(U26+V26+W26=S26,"","X")A27=CONCATENATE($C$1,1,9)B27=IF(U27+V27+W27=S27,"","X")A28=CONCATENATE($C$1,2,0)B28=IF(U28+V28+W28=S28,"","X")A29=CONCATENATE($C$1,2,1)B29=IF(U29+V29+W29=S29,"","X")A30=CONCATENATE($C$1,2,2)B30=IF(U30+V30+W30=S30,"","X")A31=CONCATENATE($C$1,2,3)B31=IF(U31+V31+W31=S31,"","X")A32=CONCATENATE($C$1,2,4)B32=IF(U32+V32+W32=S32,"","X")A33=CONCATENATE($C$1,2,5)B33=IF(U33+V33+W33=S33,"","X")A34=CONCATENATE($C$1,2,6)B34=IF(U34+V34+W34=S34,"","X")A35=CONCATENATE($C$1,2,7)B35=IF(U35+V35+W35=S35,"","X")A36=CONCATENATE($C$1,2,8)B36=IF(U36+V36+W36=S36,"","X")M9=IF(G9>0,VLOOKUP(G9,Lookups!$B$2:$G$32,3))+IF(H9>0,VLOOKUP(H9,Lookups!$B$2:$G$32,3))+IF(I9>0,VLOOKUP(I9,Lookups!$B$2:$G$32,3))+IF(J9>0,VLOOKUP(J9,Lookups!$B$2:$G$32,3))N9=IF(G9>0,VLOOKUP(G9,Lookups!B$2:$G$32,3))+IF(H9>0,VLOOKUP(H9,Lookups!$B$2:$G$32,3))+IF(I9>0,VLOOKUP(I9,Lookups!$B$2:$G$32,3))+IF(J9>0,VLOOKUP(J9,Lookups!$B$2:$G$32,3))-M9M10=IF(G10>0,VLOOKUP(G10,Lookups!$B$2:$G$32,3))+IF(H10>0,VLOOKUP(H10,Lookups!$B$2:$G$32,3))+IF(I10>0,VLOOKUP(I10,Lookups!$B$2:$G$32,3))+IF(J10>0,VLOOKUP(J10,Lookups!$B$2:$G$32,3))N10=IF(G10>0,VLOOKUP(G10,Lookups!B$2:$G$32,3))+IF(H10>0,VLOOKUP(H10,Lookups!$B$2:$G$32,3))+IF(I10>0,VLOOKUP(I10,Lookups!$B$2:$G$32,3))+IF(J10>0,VLOOKUP(J10,Lookups!$B$2:$G$32,3))-M10M11=IF(G11>0,VLOOKUP(G11,Lookups!$B$2:$G$32,3))+IF(H11>0,VLOOKUP(H11,Lookups!$B$2:$G$32,3))+IF(I11>0,VLOOKUP(I11,Lookups!$B$2:$G$32,3))+IF(J11>0,VLOOKUP(J11,Lookups!$B$2:$G$32,3))N11=IF(G11>0,VLOOKUP(G11,Lookups!B$2:$G$32,3))+IF(H11>0,VLOOKUP(H11,Lookups!$B$2:$G$32,3))+IF(I11>0,VLOOKUP(I11,Lookups!$B$2:$G$32,3))+IF(J11>0,VLOOKUP(J11,Lookups!$B$2:$G$32,3))-M11M12=IF(G12>0,VLOOKUP(G12,Lookups!$B$2:$G$32,3))+IF(H12>0,VLOOKUP(H12,Lookups!$B$2:$G$32,3))+IF(I12>0,VLOOKUP(I12,Lookups!$B$2:$G$32,3))+IF(J12>0,VLOOKUP(J12,Lookups!$B$2:$G$32,3))N12=IF(G12>0,VLOOKUP(G12,Lookups!B$2:$G$32,3))+IF(H12>0,VLOOKUP(H12,Lookups!$B$2:$G$32,3))+IF(I12>0,VLOOKUP(I12,Lookups!$B$2:$G$32,3))+IF(J12>0,VLOOKUP(J12,Lookups!$B$2:$G$32,3))-M12M13=IF(G13>0,VLOOKUP(G13,Lookups!$B$2:$G$32,3))+IF(H13>0,VLOOKUP(H13,Lookups!$B$2:$G$32,3))+IF(I13>0,VLOOKUP(I13,Lookups!$B$2:$G$32,3))+IF(J13>0,VLOOKUP(J13,Lookups!$B$2:$G$32,3))N13=IF(G13>0,VLOOKUP(G13,Lookups!B$2:$G$32,3))+IF(H13>0,VLOOKUP(H13,Lookups!$B$2:$G$32,3))+IF(I13>0,VLOOKUP(I13,Lookups!$B$2:$G$32,3))+IF(J13>0,VLOOKUP(J13,Lookups!$B$2:$G$32,3))-M13M14=IF(G14>0,VLOOKUP(G14,Lookups!$B$2:$G$32,3))+IF(H14>0,VLOOKUP(H14,Lookups!$B$2:$G$32,3))+IF(I14>0,VLOOKUP(I14,Lookups!$B$2:$G$32,3))+IF(J14>0,VLOOKUP(J14,Lookups!$B$2:$G$32,3))N14=IF(G14>0,VLOOKUP(G14,Lookups!B$2:$G$32,3))+IF(H14>0,VLOOKUP(H14,Lookups!$B$2:$G$32,3))+IF(I14>0,VLOOKUP(I14,Lookups!$B$2:$G$32,3))+IF(J14>0,VLOOKUP(J14,Lookups!$B$2:$G$32,3))-M14M15=IF(G15>0,VLOOKUP(G15,Lookups!$B$2:$G$32,3))+IF(H15>0,VLOOKUP(H15,Lookups!$B$2:$G$32,3))+IF(I15>0,VLOOKUP(I15,Lookups!$B$2:$G$32,3))+IF(J15>0,VLOOKUP(J15,Lookups!$B$2:$G$32,3))N15=IF(G15>0,VLOOKUP(G15,Lookups!B$2:$G$32,3))+IF(H15>0,VLOOKUP(H15,Lookups!$B$2:$G$32,3))+IF(I15>0,VLOOKUP(I15,Lookups!$B$2:$G$32,3))+IF(J15>0,VLOOKUP(J15,Lookups!$B$2:$G$32,3))-M15M16=IF(G16>0,VLOOKUP(G16,Lookups!$B$2:$G$32,3))+IF(H16>0,VLOOKUP(H16,Lookups!$B$2:$G$32,3))+IF(I16>0,VLOOKUP(I16,Lookups!$B$2:$G$32,3))+IF(J16>0,VLOOKUP(J16,Lookups!$B$2:$G$32,3))N16=IF(G16>0,VLOOKUP(G16,Lookups!B$2:$G$32,3))+IF(H16>0,VLOOKUP(H16,Lookups!$B$2:$G$32,3))+IF(I16>0,VLOOKUP(I16,Lookups!$B$2:$G$32,3))+IF(J16>0,VLOOKUP(J16,Lookups!$B$2:$G$32,3))-M16M17=IF(G17>0,VLOOKUP(G17,Lookups!$B$2:$G$32,3))+IF(H17>0,VLOOKUP(H17,Lookups!$B$2:$G$32,3))+IF(I17>0,VLOOKUP(I17,Lookups!$B$2:$G$32,3))+IF(J17>0,VLOOKUP(J17,Lookups!$B$2:$G$32,3))N17=IF(G17>0,VLOOKUP(G17,Lookups!B$2:$G$32,3))+IF(H17>0,VLOOKUP(H17,Lookups!$B$2:$G$32,3))+IF(I17>0,VLOOKUP(I17,Lookups!$B$2:$G$32,3))+IF(J17>0,VLOOKUP(J17,Lookups!$B$2:$G$32,3))-M17M18=IF(G18>0,VLOOKUP(G18,Lookups!$B$2:$G$32,3))+IF(H18>0,VLOOKUP(H18,Lookups!$B$2:$G$32,3))+IF(I18>0,VLOOKUP(I18,Lookups!$B$2:$G$32,3))+IF(J18>0,VLOOKUP(J18,Lookups!$B$2:$G$32,3))N18=IF(G18>0,VLOOKUP(G18,Lookups!B$2:$G$32,3))+IF(H18>0,VLOOKUP(H18,Lookups!$B$2:$G$32,3))+IF(I18>0,VLOOKUP(I18,Lookups!$B$2:$G$32,3))+IF(J18>0,VLOOKUP(J18,Lookups!$B$2:$G$32,3))-M18M19=IF(G19>0,VLOOKUP(G19,Lookups!$B$2:$G$32,3))+IF(H19>0,VLOOKUP(H19,Lookups!$B$2:$G$32,3))+IF(I19>0,VLOOKUP(I19,Lookups!$B$2:$G$32,3))+IF(J19>0,VLOOKUP(J19,Lookups!$B$2:$G$32,3))N19=IF(G19>0,VLOOKUP(G19,Lookups!B$2:$G$32,3))+IF(H19>0,VLOOKUP(H19,Lookups!$B$2:$G$32,3))+IF(I19>0,VLOOKUP(I19,Lookups!$B$2:$G$32,3))+IF(J19>0,VLOOKUP(J19,Lookups!$B$2:$G$32,3))-M19M20=IF(G20>0,VLOOKUP(G20,Lookups!$B$2:$G$32,3))+IF(H20>0,VLOOKUP(H20,Lookups!$B$2:$G$32,3))+IF(I20>0,VLOOKUP(I20,Lookups!$B$2:$G$32,3))+IF(J20>0,VLOOKUP(J20,Lookups!$B$2:$G$32,3))N20=IF(G20>0,VLOOKUP(G20,Lookups!B$2:$G$32,3))+IF(H20>0,VLOOKUP(H20,Lookups!$B$2:$G$32,3))+IF(I20>0,VLOOKUP(I20,Lookups!$B$2:$G$32,3))+IF(J20>0,VLOOKUP(J20,Lookups!$B$2:$G$32,3))-M20M21=IF(G21>0,VLOOKUP(G21,Lookups!$B$2:$G$32,3))+IF(H21>0,VLOOKUP(H21,Lookups!$B$2:$G$32,3))+IF(I21>0,VLOOKUP(I21,Lookups!$B$2:$G$32,3))+IF(J21>0,VLOOKUP(J21,Lookups!$B$2:$G$32,3))N21=IF(G21>0,VLOOKUP(G21,Lookups!B$2:$G$32,3))+IF(H21>0,VLOOKUP(H21,Lookups!$B$2:$G$32,3))+IF(I21>0,VLOOKUP(I21,Lookups!$B$2:$G$32,3))+IF(J21>0,VLOOKUP(J21,Lookups!$B$2:$G$32,3))-M21M22=IF(G22>0,VLOOKUP(G22,Lookups!$B$2:$G$32,3))+IF(H22>0,VLOOKUP(H22,Lookups!$B$2:$G$32,3))+IF(I22>0,VLOOKUP(I22,Lookups!$B$2:$G$32,3))+IF(J22>0,VLOOKUP(J22,Lookups!$B$2:$G$32,3))N22=IF(G22>0,VLOOKUP(G22,Lookups!B$2:$G$32,3))+IF(H22>0,VLOOKUP(H22,Lookups!$B$2:$G$32,3))+IF(I22>0,VLOOKUP(I22,Lookups!$B$2:$G$32,3))+IF(J22>0,VLOOKUP(J22,Lookups!$B$2:$G$32,3))-M22M23=IF(G23>0,VLOOKUP(G23,Lookups!$B$2:$G$32,3))+IF(H23>0,VLOOKUP(H23,Lookups!$B$2:$G$32,3))+IF(I23>0,VLOOKUP(I23,Lookups!$B$2:$G$32,3))+IF(J23>0,VLOOKUP(J23,Lookups!$B$2:$G$32,3))N23=IF(G23>0,VLOOKUP(G23,Lookups!B$2:$G$32,3))+IF(H23>0,VLOOKUP(H23,Lookups!$B$2:$G$32,3))+IF(I23>0,VLOOKUP(I23,Lookups!$B$2:$G$32,3))+IF(J23>0,VLOOKUP(J23,Lookups!$B$2:$G$32,3))-M23M24=IF(G24>0,VLOOKUP(G24,Lookups!$B$2:$G$32,3))+IF(H24>0,VLOOKUP(H24,Lookups!$B$2:$G$32,3))+IF(I24>0,VLOOKUP(I24,Lookups!$B$2:$G$32,3))+IF(J24>0,VLOOKUP(J24,Lookups!$B$2:$G$32,3))N24=IF(G24>0,VLOOKUP(G24,Lookups!B$2:$G$32,3))+IF(H24>0,VLOOKUP(H24,Lookups!$B$2:$G$32,3))+IF(I24>0,VLOOKUP(I24,Lookups!$B$2:$G$32,3))+IF(J24>0,VLOOKUP(J24,Lookups!$B$2:$G$32,3))-M24M25=IF(G25>0,VLOOKUP(G25,Lookups!$B$2:$G$32,3))+IF(H25>0,VLOOKUP(H25,Lookups!$B$2:$G$32,3))+IF(I25>0,VLOOKUP(I25,Lookups!$B$2:$G$32,3))+IF(J25>0,VLOOKUP(J25,Lookups!$B$2:$G$32,3))N25=IF(G25>0,VLOOKUP(G25,Lookups!B$2:$G$32,3))+IF(H25>0,VLOOKUP(H25,Lookups!$B$2:$G$32,3))+IF(I25>0,VLOOKUP(I25,Lookups!$B$2:$G$32,3))+IF(J25>0,VLOOKUP(J25,Lookups!$B$2:$G$32,3))-M25M26=IF(G26>0,VLOOKUP(G26,Lookups!$B$2:$G$32,3))+IF(H26>0,VLOOKUP(H26,Lookups!$B$2:$G$32,3))+IF(I26>0,VLOOKUP(I26,Lookups!$B$2:$G$32,3))+IF(J26>0,VLOOKUP(J26,Lookups!$B$2:$G$32,3))N26=IF(G26>0,VLOOKUP(G26,Lookups!B$2:$G$32,3))+IF(H26>0,VLOOKUP(H26,Lookups!$B$2:$G$32,3))+IF(I26>0,VLOOKUP(I26,Lookups!$B$2:$G$32,3))+IF(J26>0,VLOOKUP(J26,Lookups!$B$2:$G$32,3))-M26M27=IF(G27>0,VLOOKUP(G27,Lookups!$B$2:$G$32,3))+IF(H27>0,VLOOKUP(H27,Lookups!$B$2:$G$32,3))+IF(I27>0,VLOOKUP(I27,Lookups!$B$2:$G$32,3))+IF(J27>0,VLOOKUP(J27,Lookups!$B$2:$G$32,3))N27=IF(G27>0,VLOOKUP(G27,Lookups!B$2:$G$32,3))+IF(H27>0,VLOOKUP(H27,Lookups!$B$2:$G$32,3))+IF(I27>0,VLOOKUP(I27,Lookups!$B$2:$G$32,3))+IF(J27>0,VLOOKUP(J27,Lookups!$B$2:$G$32,3))-M27M28=IF(G28>0,VLOOKUP(G28,Lookups!$B$2:$G$32,3))+IF(H28>0,VLOOKUP(H28,Lookups!$B$2:$G$32,3))+IF(I28>0,VLOOKUP(I28,Lookups!$B$2:$G$32,3))+IF(J28>0,VLOOKUP(J28,Lookups!$B$2:$G$32,3))N28=IF(G28>0,VLOOKUP(G28,Lookups!B$2:$G$32,3))+IF(H28>0,VLOOKUP(H28,Lookups!$B$2:$G$32,3))+IF(I28>0,VLOOKUP(I28,Lookups!$B$2:$G$32,3))+IF(J28>0,VLOOKUP(J28,Lookups!$B$2:$G$32,3))-M28M29=IF(G29>0,VLOOKUP(G29,Lookups!$B$2:$G$32,3))+IF(H29>0,VLOOKUP(H29,Lookups!$B$2:$G$32,3))+IF(I29>0,VLOOKUP(I29,Lookups!$B$2:$G$32,3))+IF(J29>0,VLOOKUP(J29,Lookups!$B$2:$G$32,3))N29=IF(G29>0,VLOOKUP(G29,Lookups!B$2:$G$32,3))+IF(H29>0,VLOOKUP(H29,Lookups!$B$2:$G$32,3))+IF(I29>0,VLOOKUP(I29,Lookups!$B$2:$G$32,3))+IF(J29>0,VLOOKUP(J29,Lookups!$B$2:$G$32,3))-M29M30=IF(G30>0,VLOOKUP(G30,Lookups!$B$2:$G$32,3))+IF(H30>0,VLOOKUP(H30,Lookups!$B$2:$G$32,3))+IF(I30>0,VLOOKUP(I30,Lookups!$B$2:$G$32,3))+IF(J30>0,VLOOKUP(J30,Lookups!$B$2:$G$32,3))N30=IF(G30>0,VLOOKUP(G30,Lookups!B$2:$G$32,3))+IF(H30>0,VLOOKUP(H30,Lookups!$B$2:$G$32,3))+IF(I30>0,VLOOKUP(I30,Lookups!$B$2:$G$32,3))+IF(J30>0,VLOOKUP(J30,Lookups!$B$2:$G$32,3))-M30M31=IF(G31>0,VLOOKUP(G31,Lookups!$B$2:$G$32,3))+IF(H31>0,VLOOKUP(H31,Lookups!$B$2:$G$32,3))+IF(I31>0,VLOOKUP(I31,Lookups!$B$2:$G$32,3))+IF(J31>0,VLOOKUP(J31,Lookups!$B$2:$G$32,3))N31=IF(G31>0,VLOOKUP(G31,Lookups!B$2:$G$32,3))+IF(H31>0,VLOOKUP(H31,Lookups!$B$2:$G$32,3))+IF(I31>0,VLOOKUP(I31,Lookups!$B$2:$G$32,3))+IF(J31>0,VLOOKUP(J31,Lookups!$B$2:$G$32,3))-M31M32=IF(G32>0,VLOOKUP(G32,Lookups!$B$2:$G$32,3))+IF(H32>0,VLOOKUP(H32,Lookups!$B$2:$G$32,3))+IF(I32>0,VLOOKUP(I32,Lookups!$B$2:$G$32,3))+IF(J32>0,VLOOKUP(J32,Lookups!$B$2:$G$32,3))N32=IF(G32>0,VLOOKUP(G32,Lookups!B$2:$G$32,3))+IF(H32>0,VLOOKUP(H32,Lookups!$B$2:$G$32,3))+IF(I32>0,VLOOKUP(I32,Lookups!$B$2:$G$32,3))+IF(J32>0,VLOOKUP(J32,Lookups!$B$2:$G$32,3))-M32M33=IF(G33>0,VLOOKUP(G33,Lookups!$B$2:$G$32,3))+IF(H33>0,VLOOKUP(H33,Lookups!$B$2:$G$32,3))+IF(I33>0,VLOOKUP(I33,Lookups!$B$2:$G$32,3))+IF(J33>0,VLOOKUP(J33,Lookups!$B$2:$G$32,3))N33=IF(G33>0,VLOOKUP(G33,Lookups!B$2:$G$32,3))+IF(H33>0,VLOOKUP(H33,Lookups!$B$2:$G$32,3))+IF(I33>0,VLOOKUP(I33,Lookups!$B$2:$G$32,3))+IF(J33>0,VLOOKUP(J33,Lookups!$B$2:$G$32,3))-M33M34=IF(G34>0,VLOOKUP(G34,Lookups!$B$2:$G$32,3))+IF(H34>0,VLOOKUP(H34,Lookups!$B$2:$G$32,3))+IF(I34>0,VLOOKUP(I34,Lookups!$B$2:$G$32,3))+IF(J34>0,VLOOKUP(J34,Lookups!$B$2:$G$32,3))N34=IF(G34>0,VLOOKUP(G34,Lookups!B$2:$G$32,3))+IF(H34>0,VLOOKUP(H34,Lookups!$B$2:$G$32,3))+IF(I34>0,VLOOKUP(I34,Lookups!$B$2:$G$32,3))+IF(J34>0,VLOOKUP(J34,Lookups!$B$2:$G$32,3))-M34M35=IF(G35>0,VLOOKUP(G35,Lookups!$B$2:$G$32,3))+IF(H35>0,VLOOKUP(H35,Lookups!$B$2:$G$32,3))+IF(I35>0,VLOOKUP(I35,Lookups!$B$2:$G$32,3))+IF(J35>0,VLOOKUP(J35,Lookups!$B$2:$G$32,3))N35=IF(G35>0,VLOOKUP(G35,Lookups!B$2:$G$32,3))+IF(H35>0,VLOOKUP(H35,Lookups!$B$2:$G$32,3))+IF(I35>0,VLOOKUP(I35,Lookups!$B$2:$G$32,3))+IF(J35>0,VLOOKUP(J35,Lookups!$B$2:$G$32,3))-M35M36=IF(G36>0,VLOOKUP(G36,Lookups!$B$2:$G$32,3))+IF(H36>0,VLOOKUP(H36,Lookups!$B$2:$G$32,3))+IF(I36>0,VLOOKUP(I36,Lookups!$B$2:$G$32,3))+IF(J36>0,VLOOKUP(J36,Lookups!$B$2:$G$32,3))N36=IF(G36>0,VLOOKUP(G36,Lookups!B$2:$G$32,3))+IF(H36>0,VLOOKUP(H36,Lookups!$B$2:$G$32,3))+IF(I36>0,VLOOKUP(I36,Lookups!$B$2:$G$32,3))+IF(J36>0,VLOOKUP(J36,Lookups!$B$2:$G$32,3))-M36AB9=IF(X9>0,VLOOKUP(X9,Lookups!Q$2:W$16,7,FALSE)*Y9)+IF(Z9>0,VLOOKUP(Z9,Lookups!Q1:W8,7,FALSE)*AA9,0)AB10=IF(X10>0,VLOOKUP(X10,Lookups!Q$2:W$16,7,FALSE)*Y10)+IF(X10>0,VLOOKUP(X10,Lookups!Q2:W9,7,FALSE)*Y10,0)AB11=IF(X11>0,VLOOKUP(X11,Lookups!Q$2:W$16,7,FALSE)*Y11)+IF(X11>0,VLOOKUP(X11,Lookups!Q3:W10,7,FALSE)*Y11,0)AB12=IF(X12>0,VLOOKUP(X12,Lookups!Q$2:W$16,7,FALSE)*Y12)+IF(X12>0,VLOOKUP(X12,Lookups!Q4:W11,7,FALSE)*Y12,0)AB13=IF(X13>0,VLOOKUP(X13,Lookups!Q$2:W$16,7,FALSE)*Y13)+IF(X13>0,VLOOKUP(X13,Lookups!Q5:W12,7,FALSE)*Y13,0)AB14=IF(X14>0,VLOOKUP(X14,Lookups!Q$2:W$16,7,FALSE)*Y14)+IF(X14>0,VLOOKUP(X14,Lookups!Q6:W13,7,FALSE)*Y14,0)AB15=IF(X15>0,VLOOKUP(X15,Lookups!Q$2:W$16,7,FALSE)*Y15)+IF(X15>0,VLOOKUP(X15,Lookups!Q7:W14,7,FALSE)*Y15,0)AB16=IF(X16>0,VLOOKUP(X16,Lookups!Q$2:W$16,7,FALSE)*Y16)+IF(X16>0,VLOOKUP(X16,Lookups!Q8:W15,7,FALSE)*Y16,0)AB17=IF(X17>0,VLOOKUP(X17,Lookups!Q$2:W$16,7,FALSE)*Y17)+IF(X17>0,VLOOKUP(X17,Lookups!Q9:W16,7,FALSE)*Y17,0)AB18=IF(X18>0,VLOOKUP(X18,Lookups!Q$2:W$16,7,FALSE)*Y18)+IF(X18>0,VLOOKUP(X18,Lookups!Q10:W17,7,FALSE)*Y18,0)AB19=IF(X19>0,VLOOKUP(X19,Lookups!Q$2:W$16,7,FALSE)*Y19)+IF(X19>0,VLOOKUP(X19,Lookups!Q11:W18,7,FALSE)*Y19,0)AB20=IF(X20>0,VLOOKUP(X20,Lookups!Q$2:W$16,7,FALSE)*Y20)+IF(X20>0,VLOOKUP(X20,Lookups!Q12:W19,7,FALSE)*Y20,0)AB21=IF(X21>0,VLOOKUP(X21,Lookups!Q$2:W$16,7,FALSE)*Y21)+IF(X21>0,VLOOKUP(X21,Lookups!Q13:W20,7,FALSE)*Y21,0)AB22=IF(X22>0,VLOOKUP(X22,Lookups!Q$2:W$16,7,FALSE)*Y22)+IF(X22>0,VLOOKUP(X22,Lookups!Q14:W21,7,FALSE)*Y22,0)AB23=IF(X23>0,VLOOKUP(X23,Lookups!Q$2:W$16,7,FALSE)*Y23)+IF(X23>0,VLOOKUP(X23,Lookups!Q15:W22,7,FALSE)*Y23,0)AB24=IF(X24>0,VLOOKUP(X24,Lookups!Q$2:W$16,7,FALSE)*Y24)+IF(X24>0,VLOOKUP(X24,Lookups!Q16:W23,7,FALSE)*Y24,0)AB25=IF(X25>0,VLOOKUP(X25,Lookups!Q$2:W$16,7,FALSE)*Y25)+IF(X25>0,VLOOKUP(X25,Lookups!Q17:W24,7,FALSE)*Y25,0)AB26=IF(X26>0,VLOOKUP(X26,Lookups!Q$2:W$16,7,FALSE)*Y26)+IF(X26>0,VLOOKUP(X26,Lookups!Q18:W25,7,FALSE)*Y26,0)AB27=IF(X27>0,VLOOKUP(X27,Lookups!Q$2:W$16,7,FALSE)*Y27)+IF(X27>0,VLOOKUP(X27,Lookups!Q19:W26,7,FALSE)*Y27,0)AB28=IF(X28>0,VLOOKUP(X28,Lookups!Q$2:W$16,7,FALSE)*Y28)+IF(X28>0,VLOOKUP(X28,Lookups!Q20:W27,7,FALSE)*Y28,0)AB29=IF(X29>0,VLOOKUP(X29,Lookups!Q$2:W$16,7,FALSE)*Y29)+IF(X29>0,VLOOKUP(X29,Lookups!Q21:W28,7,FALSE)*Y29,0)AB30=IF(X30>0,VLOOKUP(X30,Lookups!Q$2:W$16,7,FALSE)*Y30)+IF(X30>0,VLOOKUP(X30,Lookups!Q22:W29,7,FALSE)*Y30,0)AB31=IF(X31>0,VLOOKUP(X31,Lookups!Q$2:W$16,7,FALSE)*Y31)+IF(X31>0,VLOOKUP(X31,Lookups!Q23:W30,7,FALSE)*Y31,0)AB32=IF(X32>0,VLOOKUP(X32,Lookups!Q$2:W$16,7,FALSE)*Y32)+IF(X32>0,VLOOKUP(X32,Lookups!Q24:W31,7,FALSE)*Y32,0)AB33=IF(X33>0,VLOOKUP(X33,Lookups!Q$2:W$16,7,FALSE)*Y33)+IF(X33>0,VLOOKUP(X33,Lookups!Q25:W32,7,FALSE)*Y33,0)AB34=IF(X34>0,VLOOKUP(X34,Lookups!Q$2:W$16,7,FALSE)*Y34)+IF(X34>0,VLOOKUP(X34,Lookups!Q26:W33,7,FALSE)*Y34,0)AB35=IF(X35>0,VLOOKUP(X35,Lookups!Q$2:W$16,7,FALSE)*Y35)+IF(X35>0,VLOOKUP(X35,Lookups!Q27:W34,7,FALSE)*Y35,0)AB36=IF(X36>0,VLOOKUP(X36,Lookups!Q$2:W$16,7,FALSE)*Y36)+IF(X36>0,VLOOKUP(X36,Lookups!Q28:W35,7,FALSE)*Y36,0)