/// <summary>
/// reset r1c1 formulas across all sheets
/// </summary>
protected void resetFormulas080911(SpreadsheetGear.IWorkbook wb, int intype, bool setloose)
{
//set loose
if (!setloose)
{
wb.Worksheets["sheet1"].Cells["d34"].Range.Value = 1;
}
else
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=VLOOKUP(R28C1,R241C18:R539C21,4,FALSE)";
}
//book calcs
wb.Worksheets["bookcalcs"].Cells["b17"].Range.FormulaR1C1 = "=VLOOKUP(Sheet1!R[15]C[4],Sheet1!R[23]C[2]:R[26]C[8],3,FALSE)";
wb.Worksheets["bookcalcs"].Cells["b18"].Range.FormulaR1C1 = "=VLOOKUP(Sheet1!R[14]C[4],Sheet1!R[22]C[2]:R[25]C[8],4,FALSE)";
wb.Worksheets["bookcalcs"].Cells["b19"].Range.FormulaR1C1 = "=VLOOKUP(Sheet1!R[13]C[4],Sheet1!R[21]C[2]:R[24]C[8],5,FALSE)";
wb.Worksheets["bookcalcs"].Cells["b20"].Range.FormulaR1C1 = "=VLOOKUP(Sheet1!R[12]C[4],Sheet1!R[20]C[2]:R[23]C[8],6,FALSE)";
wb.Worksheets["bookcalcs"].Cells["b21"].Range.FormulaR1C1 = "=VLOOKUP(Sheet1!R[11]C[4],Sheet1!R[19]C[2]:R[22]C[8],7,FALSE)";
//paper size modifies some of the input data
wb.Worksheets["uploaded"].Cells["c6"].Range.FormulaR1C1 = intype == 3 ? "=Sheet1!R[89]C[1]" : "";
wb.Worksheets["uploaded"].Cells["d6"].Range.FormulaR1C1 = intype == 3 ? "=Sheet1!R[90]C" : "";
wb.Worksheets["uploaded"].Cells["e6"].Range.FormulaR1C1 = intype == 3 ? "=Sheet1!R[91]C[-1]" : "";
wb.Worksheets["uploaded"].Cells["f6"].Range.FormulaR1C1 = intype == 3 ? "=Sheet1!R[92]C[-2]" : "";
//different formulas for paper size
if (intype != 3)
{
wb.Worksheets["sheet1"].Cells["g32"].FormulaR1C1 = "=VLOOKUP(RC[-1],R[8]C[-3]:R[10]C[-2],2,FALSE)";
wb.Worksheets["sheet1"].Cells["g33"].FormulaR1C1 = "=(R[-1]C)";
wb.Worksheets["sheet1"].Cells["E32"].FormulaR1C1 = "=(Sheet1!RC[-1])";
wb.Worksheets["sheet1"].Cells["F35"].FormulaR1C1 = "=(Sheet1!R[-2]C[1])";
}
else
{
//TempPrintOut
//wb.Worksheets["TempPrintOut"].Cells["A42"].Value = "Block Dims";
//wb.Worksheets["TempPrintOut"].Cells["A42"].Value = "Paper";
//wb.Worksheets["TempPrintOut"].Cells["B43"].Value = "weight gsm";
//wb.Worksheets["TempPrintOut"].Cells["C43"].Value = ("Sheet1!E73");
//sheet1
wb.Worksheets["sheet1"].Cells["g32"].FormulaR1C1 = "=VLOOKUP(RC[-1],R[8]C[-3]:R[10]C[-2],2,FALSE)";
wb.Worksheets["sheet1"].Cells["g33"].FormulaR1C1 = "=(R[-1]C)";
wb.Worksheets["sheet1"].Cells["E32"].FormulaR1C1 = "=(Sheet1!RC[-1])";
wb.Worksheets["sheet1"].Cells["F35"].FormulaR1C1 = "=(Sheet1!R[-2]C[1])";
wb.Worksheets["sheet1"].Cells["E70"].FormulaR1C1 = "=RC[-2]";
wb.Worksheets["sheet1"].Cells["E71"].FormulaR1C1 = "=RC[-2]";
wb.Worksheets["sheet1"].Cells["E72"].FormulaR1C1 = "=RC[-2]";
wb.Worksheets["sheet1"].Cells["E73"].FormulaR1C1 = "=R[13]C[-4]";
wb.Worksheets["sheet1"].Cells["E74"].FormulaR1C1 = "=IF(RC[-1]=True,\"Yes\",\"No\")";
}
//shipment summary
wb.Worksheets["shipmentsummary"].Cells["d3"].Range.FormulaR1C1 = "=BookCalcs!R[7]C[6]";
wb.Worksheets["shipmentsummary"].Cells["D5"].Range.FormulaR1C1 = "=BookCalcs!R[4]C[1]";
wb.Worksheets["shipmentsummary"].Cells["D7"].Range.FormulaR1C1 = "=BookCalcs!R[23]C[9]";
wb.Worksheets["shipmentsummary"].Cells["D8"].Range.FormulaR1C1 = "";
wb.Worksheets["shipmentsummary"].Cells["D9"].Range.FormulaR1C1 = "=BookCalcs!R[21]C[11]";
wb.Worksheets["shipmentsummary"].Cells["D11"].Range.FormulaR1C1 = "=BookCalcs!R[25]C[9]";
wb.Worksheets["shipmentsummary"].Cells["D13"].Range.FormulaR1C1 = "=BookCalcs!R[11]C[11]";
wb.Worksheets["shipmentsummary"].Cells["D15"].Range.FormulaR1C1 = "=BookCalcs!R[8]C[5]";
wb.Worksheets["shipmentsummary"].Cells["D17"].Range.FormulaR1C1 = "=BookCalcs!R[1]C[9]";
wb.Worksheets["shipmentsummary"].Cells["D19"].Range.FormulaR1C1 = "=BookCalcs!R[17]C[15]";
wb.Worksheets["shipmentsummary"].Cells["D21"].Range.FormulaR1C1 = "=BookCalcs!R[9]C[15]";
wb.Worksheets["shipmentsummary"].Cells["D23"].Range.FormulaR1C1 = "=ROUND(BookCalcs!R[19]C[15],3)";
wb.Worksheets["shipmentsummary"].Cells["I3"].Range.FormulaR1C1 = "=BookCalcs!R[3]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I5"].Range.FormulaR1C1 = "=BookCalcs!R[-1]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I7"].Range.FormulaR1C1 = "=BookCalcs!R[-2]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I9"].Range.FormulaR1C1 = "=BookCalcs!R[-2]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I11"].Range.FormulaR1C1 = "=IF(BookCalcs!R[19]C[7]>0,1,0)";
wb.Worksheets["shipmentsummary"].Cells["I13"].Range.FormulaR1C1 = "=BookCalcs!R[17]C[7]";
wb.Worksheets["shipmentsummary"].Cells["I15"].Range.FormulaR1C1 = "=BookCalcs!R[15]C[9]";
wb.Worksheets["shipmentsummary"].Cells["I17"].Range.FormulaR1C1 = "=BookCalcs!R[19]C[6]";
wb.Worksheets["shipmentsummary"].Cells["I19"].Range.FormulaR1C1 = "=BookCalcs!R[-6]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I21"].Range.FormulaR1C1 = "=ROUND(BookCalcs!R[-10]C[-4],3)";
wb.Worksheets["shipmentsummary"].Cells["I23"].Range.FormulaR1C1 = "=ROUND(R[-2]C/(R[-4]C/1000),3)";
//Reset the price calculations
wb.Worksheets["prices"].Cells["J178"].Range.FormulaR1C1 = "=VLOOKUP(RC[-3],R[-15]C[-3]:R[-2]C,4,FALSE)";
wb.Worksheets["prices"].Cells["E194"].Range.FormulaR1C1 = "=IF(Sheet1!R[-169]C[-4]=\"Dubai\",\"Pre-Palletised Only\",IF(Sheet1!R[-160]C[-1]=1,\"Pre-Palletised Only\",\"Shipped As Loose Cartons\"))";
wb.Worksheets["prices"].Cells["R206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-8],R[-8]C[-8]:R[-2]C[3],9,FALSE)";
wb.Worksheets["prices"].Cells["S206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-9],R[-8]C[-9]:R[-2]C[2],10,FALSE)";
wb.Worksheets["prices"].Cells["T206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-10],R[-8]C[-10]:R[-2]C[1],11,FALSE)";
wb.Worksheets["prices"].Cells["U206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-11],R[-8]C[-11]:R[-2]C,12,FALSE)";
wb.Worksheets["prices"].Cells["J206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-3],R[-8]C[-3]:R[-1]C,4,FALSE)";
wb.Worksheets["prices"].Cells["J217"].Range.FormulaR1C1 = "=VLOOKUP(RC[-3],R[-8]C[-3]:R[-2]C,4,FALSE)";
wb.Worksheets["prices"].Cells["R217"].Range.FormulaR1C1 = "=IF(R[-23]C[-13]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-8],R[-8]C[-8]:R[-2]C[3],9,FALSE),\" \")";
wb.Worksheets["prices"].Cells["S217"].Range.FormulaR1C1 = "=IF(R[-23]C[-14]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-9],R[-8]C[-9]:R[-2]C[2],10,FALSE),\" \")";
wb.Worksheets["prices"].Cells["T217"].Range.FormulaR1C1 = "=IF(R[-23]C[-15]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-10],R[-8]C[-10]:R[-2]C[1],11,FALSE),\" \")";
wb.Worksheets["prices"].Cells["U217"].Range.FormulaR1C1 = "=IF(R[-23]C[-16]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-11],R[-8]C[-11]:R[-2]C,12,FALSE),\" \")";
wb.Worksheets["prices"].Cells["p219"].Range.FormulaR1C1 = "=IF(R[-25]C[-11]=\"Shipped as loose cartons\",\"Loose cartons\",\"Pre-palletised\")";
wb.Worksheets["prices"].Cells["R219"].Range.FormulaR1C1 = "=IF(R[-25]C[-13]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["s219"].Range.FormulaR1C1 = "=IF(R[-25]C[-14]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["T219"].Range.FormulaR1C1 = "=IF(R[-25]C[-15]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["u219"].Range.FormulaR1C1 = "=IF(R[-25]C[-16]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["G221"].Range.FormulaR1C1 = "=IF(R[-27]C[-2]=\"Shipped As Loose Cartons\",SUM(ROUND(R[-3]C,3)*100),\" \")";
wb.Worksheets["prices"].Cells["H221"].Range.FormulaR1C1 = "=IF(R[-27]C[-3]=\"Shipped As Loose Cartons\",SUM(ROUND(R[-3]C,3)*100),\" \")";
wb.Worksheets["prices"].Cells["I221"].Range.FormulaR1C1 = "=IF(R[-27]C[-4]=\"Shipped As Loose Cartons\",SUM(ROUND(R[-3]C,3)*100),\" \")";
wb.Worksheets["prices"].Cells["G223"].Range.FormulaR1C1 = "=Uploaded!R[-217]C[10]";
wb.Worksheets["prices"].Cells["G224"].Range.FormulaR1C1 = "=IF(R[-1]C=\"Euros (cents)\",R[-3]C[-1],IF(R[-1]C=\"Sterling (pence)\",R[-3]C,IF(R[-1]C=\"US Dollars (cents)\",R[-3]C[1],R[-3]C[2])))";
wb.Worksheets["prices"].Cells["G225"].Range.FormulaR1C1 = "=VLOOKUP(R[-2]C,R[-40]C[-4]:R[-37]C[-3],2,FALSE)";
wb.Worksheets["prices"].Cells["H224"].Range.FormulaR1C1 = "=R[-3]C";
wb.Worksheets["prices"].Cells["G225"].Range.FormulaR1C1 = "=VLOOKUP(R[-2]C,R[-40]C[-4]:R[-37]C[-3],2,FALSE)";
wb.Worksheets["prices"].Cells["F227"].Range.FormulaR1C1 = "=Sum(Round(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["G227"].Range.FormulaR1C1 = "=SUM(ROUND(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["H227"].Range.FormulaR1C1 = "=SUM(ROUND(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["I227"].Range.FormulaR1C1 = "=SUM(ROUND(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["G228"].Range.FormulaR1C1 = "=IF(R[-5]C=\"Euros (cents)\",R[-1]C[-1],IF(R[-5]C=\"Sterling (pence)\",R[-1]C,IF(R[-5]C=\"Us Dollars (cents)\",R[-1]C[1],R[-1]C[2])))";
wb.Worksheets["prices"].Cells["H228"].Range.FormulaR1C1 = "=R[-1]C";
wb.Worksheets["prices"].Cells["G231"].Range.FormulaR1C1 = "=ROUND((R[-1]C/100)*Uploaded!R[-225]C[9],2)";
wb.Worksheets["prices"].Cells["H231"].Range.FormulaR1C1 = "=ROUND((R[-1]C/100)*Uploaded!R[-225]C[8],2)";
wb.Worksheets["prices"].Cells["G232"].Range.FormulaR1C1 = "=VLOOKUP(R[-46]C[-5],R[-47]C[-4]:R[-44]C[-1],4,FALSE)";
wb.Worksheets["prices"].Cells["H232"].Range.FormulaR1C1 = "=R[-44]C[-2]";
wb.Worksheets["prices"].Cells["K228"].Range.FormulaR1C1 = "=CONCATENATE(R[2]C[-4],R[-1]C[2],R[-3]C[-4])";
wb.Worksheets["prices"].Cells["K229"].Range.FormulaR1C1 = "=CONCATENATE(R[2]C[-4],R[-2]C[2],R[3]C[-4])";
wb.Worksheets["prices"].Cells["R229"].Range.FormulaR1C1 = "=IF(Sheet1!R[-220]C[-11]=0,CONCATENATE(Prices!R[-2]C[3],Prices!R[-2]C[2],Prices!R[-1]C[3]),CONCATENATE(Prices!R[-2]C[3],Prices!R[-2]C[2],Prices!R[-2]C,Prices!R[-2]C[2],Prices!R[-2]C[1]))";
//sorting
//SpreadsheetGear.SortKey _key1 = new SpreadsheetGear.SortKey(6, SpreadsheetGear.SortOrder.Ascending, SpreadsheetGear.SortDataOption.Normal);
//SpreadsheetGear.SortKey _key2 = new SpreadsheetGear.SortKey(9, SpreadsheetGear.SortOrder.Ascending, SpreadsheetGear.SortDataOption.Normal);
//SpreadsheetGear.SortKey[] _keys = {_key1, _key2};
//wb.Worksheets["prices"].Range["A162:U177"].Sort(SpreadsheetGear.SortOrientation.Rows, false ,_keys);
//Range("A162:U177").sort Key1:=.Range("G163"), _
// Order1:=xlAscending, Key2:=.Range("J163"), _
//Order2:=xlAscending, Header:=xlGuess, _
//OrderCustom:=1, MatchCase:=False, _
//Orientation:=xlTopToBottom
//this is done after sortkeys in original pricer
wb.Worksheets["prices"].Cells["E195"].Range.FormulaR1C1 = "=IF(R194C5=\"Shipped As Loose Cartons\",R[22]C[5],\"Shipment As Loose Cartons Not Available\")";
wb.Worksheets["prices"].Cells["G230"].Range.FormulaR1C1 = "=IF(Sheet1!R[-196]C[-3]=3,R[-6]C,IF(R[-6]C=\" \",R[-2]C,IF(R[-6]C<R[-2]C,R[-6]C,R[-2]C)))";
wb.Worksheets["prices"].Cells["H230"].Range.FormulaR1C1 = "=IF(Sheet1!R[-196]C[-4]=3,R[-9]C,IF(R[-6]C[-1]=\" \",R[-2]C,IF(R[-6]C<R[-2]C,R[-6]C,R[-2]C)))";
//what is this for?!
wb.Worksheets["pricessummary"].Range["A2:AS17"].Copy(wb.Worksheets["pricessummary"].Range["A52"]);
wb.Worksheets["pricessummary"].Range["A52:AS65"].Copy(wb.Worksheets["pricessummary"].Range["A71"]);
wb.Worksheets["pricessummary"].Range["A37:AS43"].Copy(wb.Worksheets["pricessummary"].Range["A81"]);
wb.Save();
}
//end reset pricer
/// <summary>
/// reset r1c1 formulas across all sheets
/// </summary>
//protected void resetFormulas(SpreadsheetGear.IWorkbook wb, int intype, bool setloose, string origin)
protected void resetFormulas(SpreadsheetGear.IWorkbook wb, int intype, string origin, bool isHCP)
{
//deprecated 14032012
//set loose
//if (!setloose)
//{
// wb.Worksheets["sheet1"].Cells["d34"].Range.Value = 1;
//}
//else
//{
// wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=VLOOKUP(R28C1,R241C18:R539C21,4,FALSE)";
//}
//string _s = origin.ToLower();
string _s = origin.ToLower().Replace("port", "").Trim();
switch (_s)
{
case "fob hong kong": //case "fob hong kong port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=VLOOKUP(R[-6]C[-3],R[207]C[14]:R[566]C[20],4,FALSE)";
break;
}
case "fob bangkok": //case "fob bangkok port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=2";
break;
}
case "fob mumbai": //case "fob mumbai port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
case "fob chennai": //case "fob chennai port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
case "fob shanghai": //case "fob shanghai port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
case "fob kaohsiung": //case "fob kaohsiung port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
case "fob keelung": //case "fob keelung port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
case "fob port kelang": //case "fob port kelang":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=VLOOKUP(R[-6]C[-3],R[207]C[14]:R[566]C[20],4,FALSE)";
break;
}
case "fob dubai ": // case "fob dubai port":
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
default:
{
wb.Worksheets["sheet1"].Cells["d34"].FormulaR1C1 = "=1";
break;
}
}
//end switch
setloose(wb);
if (intype == 3)
{
//paper size modifies some of the input data
wb.Worksheets["uploaded"].Cells["c6"].FormulaR1C1 = intype == 3 ? "=Sheet1!R[89]C[1]" : "";
wb.Worksheets["uploaded"].Cells["d6"].FormulaR1C1 = intype == 3 ? "=Sheet1!R[90]C" : "";
wb.Worksheets["uploaded"].Cells["e6"].FormulaR1C1 = intype == 3 ? "=Sheet1!R[91]C[-1]" : "";
wb.Worksheets["uploaded"].Cells["f6"].FormulaR1C1 = intype == 3 ? "=Sheet1!R[92]C[-2]" : "";
wb.Worksheets["sheet1"].Cells["g32"].FormulaR1C1 = "=VLOOKUP(RC[-1],R[8]C[-3]:R[10]C[-2],2,FALSE)";
wb.Worksheets["sheet1"].Cells["g33"].FormulaR1C1 = "=(R[-1]C)"; //"=G32";
wb.Worksheets["sheet1"].Cells["E32"].FormulaR1C1 = "=(Sheet1!RC[-1])";
wb.Worksheets["sheet1"].Cells["F35"].FormulaR1C1 = "=(Sheet1!R[-2]C[1])";
wb.Worksheets["sheet1"].Cells["E70"].FormulaR1C1 = "=RC[-2]";
wb.Worksheets["sheet1"].Cells["E71"].FormulaR1C1 = "=RC[-2]";
wb.Worksheets["sheet1"].Cells["E72"].FormulaR1C1 = "=RC[-2]";
wb.Worksheets["sheet1"].Cells["E73"].FormulaR1C1 = "=R[13]C[-4]";
wb.Worksheets["sheet1"].Cells["E74"].FormulaR1C1 = "=IF(RC[-1]=True,\"Yes\",\"No\")";
}
else
{
wb.Worksheets["sheet1"].Cells["g32"].FormulaR1C1 = "=VLOOKUP(RC[-1],R[8]C[-3]:R[10]C[-2],2,FALSE)";
wb.Worksheets["sheet1"].Cells["g33"].FormulaR1C1 = "=(R[-1]C)";
wb.Worksheets["sheet1"].Cells["E32"].FormulaR1C1 = "=(Sheet1!RC[-1])";
wb.Worksheets["sheet1"].Cells["F35"].FormulaR1C1 = "=(Sheet1!R[-2]C[1])";
}
//shipment summary
wb.Worksheets["shipmentsummary"].Cells["d3"].Range.FormulaR1C1 = "=BookCalcs!R[7]C[6]";
wb.Worksheets["shipmentsummary"].Cells["D5"].Range.FormulaR1C1 = "=BookCalcs!R[4]C[1]";
wb.Worksheets["shipmentsummary"].Cells["D7"].Range.FormulaR1C1 = "=BookCalcs!R[23]C[9]";
wb.Worksheets["shipmentsummary"].Cells["D8"].Range.FormulaR1C1 = "";
wb.Worksheets["shipmentsummary"].Cells["D9"].Range.FormulaR1C1 = "=BookCalcs!R[21]C[11]";
wb.Worksheets["shipmentsummary"].Cells["D11"].Range.FormulaR1C1 = "=BookCalcs!R[25]C[9]";
wb.Worksheets["shipmentsummary"].Cells["D13"].Range.FormulaR1C1 = "=BookCalcs!R[11]C[11]";
wb.Worksheets["shipmentsummary"].Cells["D15"].Range.FormulaR1C1 = "=BookCalcs!R[8]C[5]";
wb.Worksheets["shipmentsummary"].Cells["D17"].Range.FormulaR1C1 = "=BookCalcs!R[1]C[9]";
wb.Worksheets["shipmentsummary"].Cells["D19"].Range.FormulaR1C1 = "=BookCalcs!R[17]C[15]";
wb.Worksheets["shipmentsummary"].Cells["D21"].Range.FormulaR1C1 = "=BookCalcs!R[9]C[15]";
wb.Worksheets["shipmentsummary"].Cells["D23"].Range.FormulaR1C1 = "=ROUND(BookCalcs!R[19]C[15],3)";
wb.Worksheets["shipmentsummary"].Cells["I3"].Range.FormulaR1C1 = "=BookCalcs!R[3]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I5"].Range.FormulaR1C1 = "=BookCalcs!R[-1]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I7"].Range.FormulaR1C1 = "=BookCalcs!R[-2]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I9"].Range.FormulaR1C1 = "=BookCalcs!R[-2]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I11"].Range.FormulaR1C1 = "=IF(BookCalcs!R[19]C[7]>0,1,0)";
wb.Worksheets["shipmentsummary"].Cells["I13"].Range.FormulaR1C1 = "=BookCalcs!R[17]C[7]";
wb.Worksheets["shipmentsummary"].Cells["I15"].Range.FormulaR1C1 = "=BookCalcs!R[15]C[9]";
wb.Worksheets["shipmentsummary"].Cells["I17"].Range.FormulaR1C1 = "=BookCalcs!R[19]C[6]";
wb.Worksheets["shipmentsummary"].Cells["I19"].Range.FormulaR1C1 = "=BookCalcs!R[-6]C[-4]";
wb.Worksheets["shipmentsummary"].Cells["I21"].Range.FormulaR1C1 = "=ROUND(BookCalcs!R[-10]C[-4],3)";
wb.Worksheets["shipmentsummary"].Cells["I23"].Range.FormulaR1C1 = "=ROUND(R[-2]C/(R[-4]C/1000),3)";
//Reset the price calculations
wb.Worksheets["prices"].Cells["J178"].Range.FormulaR1C1 = "=VLOOKUP(RC[-3],R[-15]C[-3]:R[-2]C,4,FALSE)";
wb.Worksheets["prices"].Cells["E194"].Range.FormulaR1C1 = "=IF(Sheet1!R[-169]C[-4]=\"Dubai\",\"Pre-Palletised Only\",IF(Sheet1!R[-160]C[-1]=1,\"Pre-Palletised Only\",\"Shipped As Loose Cartons\"))";
wb.Worksheets["prices"].Cells["R206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-8],R[-8]C[-8]:R[-2]C[3],9,FALSE)";
wb.Worksheets["prices"].Cells["S206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-9],R[-8]C[-9]:R[-2]C[2],10,FALSE)";
wb.Worksheets["prices"].Cells["T206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-10],R[-8]C[-10]:R[-2]C[1],11,FALSE)";
wb.Worksheets["prices"].Cells["U206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-11],R[-8]C[-11]:R[-2]C,12,FALSE)";
wb.Worksheets["prices"].Cells["J206"].Range.FormulaR1C1 = "=VLOOKUP(RC[-3],R[-8]C[-3]:R[-1]C,4,FALSE)";
wb.Worksheets["prices"].Cells["J217"].Range.FormulaR1C1 = "=VLOOKUP(RC[-3],R[-8]C[-3]:R[-2]C,4,FALSE)";
wb.Worksheets["prices"].Cells["R217"].Range.FormulaR1C1 = "=IF(R[-23]C[-13]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-8],R[-8]C[-8]:R[-2]C[3],9,FALSE),\" \")";
wb.Worksheets["prices"].Cells["S217"].Range.FormulaR1C1 = "=IF(R[-23]C[-14]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-9],R[-8]C[-9]:R[-2]C[2],10,FALSE),\" \")";
wb.Worksheets["prices"].Cells["T217"].Range.FormulaR1C1 = "=IF(R[-23]C[-15]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-10],R[-8]C[-10]:R[-2]C[1],11,FALSE),\" \")";
wb.Worksheets["prices"].Cells["U217"].Range.FormulaR1C1 = "=IF(R[-23]C[-16]=\"Shipped As Loose Cartons\",VLOOKUP(RC[-11],R[-8]C[-11]:R[-2]C,12,FALSE),\" \")";
wb.Worksheets["prices"].Cells["p219"].Range.FormulaR1C1 = "=IF(R[-25]C[-11]=\"Shipped as loose cartons\",\"Loose cartons\",\"Pre-palletised\")";
wb.Worksheets["prices"].Cells["R219"].Range.FormulaR1C1 = "=IF(R[-25]C[-13]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["s219"].Range.FormulaR1C1 = "=IF(R[-25]C[-14]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["T219"].Range.FormulaR1C1 = "=IF(R[-25]C[-15]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["u219"].Range.FormulaR1C1 = "=IF(R[-25]C[-16]=\"Shipped as loose cartons\",R[-2]C,R[-13]C)";
wb.Worksheets["prices"].Cells["G221"].Range.FormulaR1C1 = "=IF(R[-27]C[-2]=\"Shipped As Loose Cartons\",SUM(ROUND(R[-3]C,3)*100),\" \")";
wb.Worksheets["prices"].Cells["H221"].Range.FormulaR1C1 = "=IF(R[-27]C[-3]=\"Shipped As Loose Cartons\",SUM(ROUND(R[-3]C,3)*100),\" \")";
wb.Worksheets["prices"].Cells["I221"].Range.FormulaR1C1 = "=IF(R[-27]C[-4]=\"Shipped As Loose Cartons\",SUM(ROUND(R[-3]C,3)*100),\" \")";
wb.Worksheets["prices"].Cells["G223"].Range.FormulaR1C1 = "=Uploaded!R[-217]C[10]";
wb.Worksheets["prices"].Cells["G224"].Range.FormulaR1C1 = "=IF(R[-1]C=\"Euros (cents)\",R[-3]C[-1],IF(R[-1]C=\"Sterling (pence)\",R[-3]C,IF(R[-1]C=\"US Dollars (cents)\",R[-3]C[1],R[-3]C[2])))";
wb.Worksheets["prices"].Cells["G225"].Range.FormulaR1C1 = "=VLOOKUP(R[-2]C,R[-40]C[-4]:R[-37]C[-3],2,FALSE)";
wb.Worksheets["prices"].Cells["H224"].Range.FormulaR1C1 = "=R[-3]C";
wb.Worksheets["prices"].Cells["G225"].Range.FormulaR1C1 = "=VLOOKUP(R[-2]C,R[-40]C[-4]:R[-37]C[-3],2,FALSE)";
wb.Worksheets["prices"].Cells["F227"].Range.FormulaR1C1 = "=Sum(Round(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["G227"].Range.FormulaR1C1 = "=SUM(ROUND(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["H227"].Range.FormulaR1C1 = "=SUM(ROUND(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["I227"].Range.FormulaR1C1 = "=SUM(ROUND(R[-20]C,3)*100)";
wb.Worksheets["prices"].Cells["G228"].Range.FormulaR1C1 = "=IF(R[-5]C=\"Euros (cents)\",R[-1]C[-1],IF(R[-5]C=\"Sterling (pence)\",R[-1]C,IF(R[-5]C=\"Us Dollars (cents)\",R[-1]C[1],R[-1]C[2])))";
wb.Worksheets["prices"].Cells["H228"].Range.FormulaR1C1 = "=R[-1]C";
wb.Worksheets["prices"].Cells["G231"].Range.FormulaR1C1 = "=ROUND((R[-1]C/100)*Uploaded!R[-225]C[9],2)";
wb.Worksheets["prices"].Cells["H231"].Range.FormulaR1C1 = "=ROUND((R[-1]C/100)*Uploaded!R[-225]C[8],2)";
wb.Worksheets["prices"].Cells["G232"].Range.FormulaR1C1 = "=VLOOKUP(R[-46]C[-5],R[-47]C[-4]:R[-44]C[-1],4,FALSE)";
wb.Worksheets["prices"].Cells["H232"].Range.FormulaR1C1 = "=R[-44]C[-2]";
wb.Worksheets["prices"].Cells["K228"].Range.FormulaR1C1 = "=CONCATENATE(R[2]C[-4],R[-1]C[2],R[-3]C[-4])";
wb.Worksheets["prices"].Cells["K229"].Range.FormulaR1C1 = "=CONCATENATE(R[2]C[-4],R[-2]C[2],R[3]C[-4])";
wb.Worksheets["prices"].Cells["R229"].Range.FormulaR1C1 = "=IF(Sheet1!R[-220]C[-11]=0,CONCATENATE(Prices!R[-2]C[3],Prices!R[-2]C[2],Prices!R[-1]C[3]),CONCATENATE(Prices!R[-2]C[3],Prices!R[-2]C[2],Prices!R[-2]C,Prices!R[-2]C[2],Prices!R[-2]C[1]))";
//sorting
//SpreadsheetGear.SortKey _key1 = new SpreadsheetGear.SortKey(6, SpreadsheetGear.SortOrder.Ascending, SpreadsheetGear.SortDataOption.Normal);
//SpreadsheetGear.SortKey _key2 = new SpreadsheetGear.SortKey(9, SpreadsheetGear.SortOrder.Ascending, SpreadsheetGear.SortDataOption.Normal);
//SpreadsheetGear.SortKey[] _keys = {_key1, _key2};
//wb.Worksheets["prices"].Range["A162:U177"].Sort(SpreadsheetGear.SortOrientation.Rows, false ,_keys);
//Range("A162:U177").sort Key1:=.Range("G163"), _
// Order1:=xlAscending, Key2:=.Range("J163"), _
//Order2:=xlAscending, Header:=xlGuess, _
//OrderCustom:=1, MatchCase:=False, _
//Orientation:=xlTopToBottom
//this is done after sortkeys in original pricer
wb.Worksheets["prices"].Cells["E195"].Range.FormulaR1C1 = "=IF(R194C5=\"Shipped As Loose Cartons\",R[22]C[5],\"Shipment As Loose Cartons Not Available\")";
wb.Worksheets["prices"].Cells["G230"].Range.FormulaR1C1 = "=IF(Sheet1!R[-196]C[-3]=3,R[-6]C,IF(R[-6]C=\" \",R[-2]C,IF(R[-6]C<R[-2]C,R[-6]C,R[-2]C)))";
wb.Worksheets["prices"].Cells["H230"].Range.FormulaR1C1 = "=IF(Sheet1!R[-196]C[-4]=3,R[-9]C,IF(R[-6]C[-1]=\" \",R[-2]C,IF(R[-6]C<R[-2]C,R[-6]C,R[-2]C)))";
//what is this for?!
wb.Worksheets["pricessummary"].Range["A2:AS17"].Copy(wb.Worksheets["pricessummary"].Range["A52"]);
wb.Worksheets["pricessummary"].Range["A52:AS65"].Copy(wb.Worksheets["pricessummary"].Range["A71"]);
wb.Worksheets["pricessummary"].Range["A37:AS43"].Copy(wb.Worksheets["pricessummary"].Range["A81"]);
//WHY do we keep neding to do this?!
wb.Worksheets["prices"].Cells["g230"].Formula = "=IF(Sheet1!D34=3,G224,IF(G224=\" \",G228,IF(G224<G228,G224,G228)))";
wb.Save();
}
