消除 Ref# 参考不存在问题

In the UpdateDemogInfoDA脚本中，我允许用户输入新信息，在另一个工作表 (JBD) 中捕获该信息，然后使用“示例数据”选项卡中的新信息覆盖旧信息。

然后，当按下新的查找按钮时，将根据以下公式查找CustID填充。由于某种原因，并且仅对于某些单元格（例如“方向”和“键码”），数据会转换为：

参考#！参考不存在错误。

我不明白为什么。我包括了这两个的代码NameSearchDA和UpdateDemogInfoDA希望我遗漏了一些对你来说显而易见的东西。我也愿意接受任何有关清理它的建议，以便它运行得更快。

我已确保示例数据中的所有单元格都不会通过运行进行合并.breakapart()。我已确保 JBD 单元引用从客户中心提取正确单元的数据。

我的电子表格是 https://docs.google.com/spreadsheets/d/1noCiboVB5_yTq9H0W_-q9goqc4E9Gq0DhcNMuXj9Kz4/edit#gid=622975187>

这 3 个选项卡是 DA 客户中心、JBD（用于复制数据的临时保存位置）和样本数据（主数据库）。

我通常使用 Jay R Levesque 作为我的样本客户。如果您点击“按名称搜索”按钮，则可以选择用户，然后从下拉列表中选择地址。这将填充该 CUST ID，然后所有公式都从该唯一 ID 驱动。

    function UpdateDemogInfoDA() {
    
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('Sample Data');
    var rg1=sh1.getDataRange();
    var vA=rg1.getValues();
    var sh2=ss.getSheetByName('JBD');
    var id=sh2.getRange('DU2').getValue();
    var JBDdata=sh2.getRange(2,2,1,137);
      
       
      
    for(var i=0;i<vA.length;i++) {
    if(vA[i][0]==id) {
    JBDdata.copyTo(sh1.getRange(i+1,2,1,137),{contentsOnly:true});
    ss.toast("Match found. Demographics Updated");
    
        break;
         }
     }

按名称搜索的代码

function NameSearchDA(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("DA Customer Center");
var filters = ss.getSheetByName("Filters");

var name = sheet.getRange("C9");
var allnames = filters.getRange("A2");
var address=sheet.getRange("C11");
var name2address=filters.getRange("E2");
var valname = allnames.getDataValidation();
var valaddress=name2address.getDataValidation();

var custid=sheet.getRange("H7");
var contractrenewal=sheet.getRange("H8");
var name = sheet.getRange("C9");
var alladdress = filters.getRange("A12");
var prevcust=sheet.getRange("C10");
var address=sheet.getRange("C11");
var zip=sheet.getRange("C12");
var phone = sheet.getRange("C13");
var email = sheet.getRange("C14");
var city=sheet.getRange("E12");
var source=sheet.getRange("H12");
var altphone=sheet.getRange("G13");
var altemail=sheet.getRange("G14");
var altcontact=sheet.getRange("C15");
var type=sheet.getRange("G15");
var contactphone1=sheet.getRange("C16");
var contactphone2=sheet.getRange("G16");
var contactemail=sheet.getRange("C17");
var mailbox=sheet.getRange("C19");
var ccbox=sheet.getRange("E19");
var emailbillbox=sheet.getRange("G19");
var mailingaddress=sheet.getRange("C20");
var startdate=sheet.getRange("H20");
var wintermailaddress=sheet.getRange("C21");
var enddate=sheet.getRange("H21");
var keycode=sheet.getRange("G22");
var directions=sheet.getRange("C23");

var custidmatch = "=iferror(index('Sample Data'!$A:$Q,Match($C$9&$C$11,'Sample Data'!$D:$D&'Sample Data'!$F:$F,0),1))";
var contractrenewdatematch = "=if(H7>0,filter('Sample Data'!$B:$B,'Sample Data'!$A:$A=$H$7),\"\")";
var prevcustmatch = "=if(H7>0, filter('Sample Data'!$C:$C,'Sample Data'!$A:$A=$H$7),\"\")";
var zipmatch = "=if(H7>0, filter('Sample Data'!$G:$G,'Sample Data'!$A:$A=$H$7),\"\")";
var citymatch = "=if(H7>0, filter('Sample Data'!$DX2:$DX8499,'Sample Data'!$A2:$A8499=$H$7),\"\")";
var sourcematch = "=if(H7>0, filter('Sample Data'!$AI2:$AI8499,'Sample Data'!$A2:$A8499=$H$7),\"\")";
var phonematch = "=if(H7>0, filter('Sample Data'!$E$2:$E$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var altphonematch = "=if(H7>0, filter('Sample Data'!$DD$2:$DD$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var emailmatch = "=if(H7>0, filter('Sample Data'!$K$2:$K$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var altemailmatch = "=if(H7>0, filter('Sample Data'!$DE$2:$DE$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var altcontactmatch = "=if(H7>0, filter('Sample Data'!$DF$2:$DF$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var typematch = "=if(H7>0, filter('Sample Data'!$DG$2:$DG$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var contactphone1match = "=if(H7>0, filter('Sample Data'!$DH$2:$DH$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var contactphone2match = "=if(H7>0, filter('Sample Data'!$DI$2:$DI$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var contactemailmatch = "=if(H7>0, filter('Sample Data'!$DJ$2:$DJ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var mailboxmatch = "=if(H7>0, filter('Sample Data'!$EF$2:$EF$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ccboxmatch = "=if(H7>0, filter('Sample Data'!$EG$2:$EG$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var emailbillsboxmatch = "=if(H7>0, filter('Sample Data'!$DP$2:$DP$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var mailaddressmatch = "=if(H7>0, filter('Sample Data'!$J$2:$J$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var wintermailaddressmatch = "=if(H7>0, filter('Sample Data'!$DS$2:$DS$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var startdatematch = "=if(H7>0, filter('Sample Data'!$DQ$2:$DQ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var enddatematch = "=if(H7>0, filter('Sample Data'!$DR$2:$DR$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var keycodematch = "=if(H7>0, filter('Sample Data'!$DO$2:$DO$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var directionsmatch = "=if(H7>0, filter('Sample Data'!$O$2:$O$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";

custid.setFormula(custidmatch);
contractrenewal.setFormula(contractrenewdatematch);
prevcust.setFormula(prevcustmatch);
zip.setFormula(zipmatch);
city.setFormula(citymatch);
source.setFormula(sourcematch);
phone.setFormula(phonematch);
altphone.setFormula(altphonematch);  
email.setFormula(emailmatch);
altemail.setFormula(altemailmatch);
altcontact.setFormula(altcontactmatch);
type.setFormula(typematch);
contactphone1.setFormula(contactphone1match);
contactphone2.setFormula(contactphone2match);
contactemail.setFormula(contactemailmatch);
mailbox.setFormula(mailboxmatch);
ccbox.setFormula(ccboxmatch);
emailbillbox.setFormula(emailbillsboxmatch);
mailingaddress.setFormula(mailaddressmatch);
wintermailaddress.setFormula(wintermailaddressmatch);
keycode.setFormula(keycodematch);
startdate.setFormula(startdatematch);
enddate.setFormula(enddatematch);
directions.setFormula(directionsmatch);

name.setDataValidation(valname);
address.setDataValidation(valaddress);

var size1= sheet.getRange("K9");
var size2= sheet.getRange("K10");
var size3= sheet.getRange("K11");
var size4= sheet.getRange("K12");
var st1=sheet.getRange("L9");
var st2=sheet.getRange("L10");
var st3=sheet.getRange("L11");
var st4=sheet.getRange("L12");
var uvbulb=sheet.getRange("L13");
var ht1=sheet.getRange("N9");
var ht2=sheet.getRange("N10");
var ht3=sheet.getRange("N11");
var ht4=sheet.getRange("N12");
var mod1=sheet.getRange("P9");
var mod2=sheet.getRange("P10");
var mod3=sheet.getRange("P11");
var mod4=sheet.getRange("P12");
var uvmod=sheet.getRange("P13");
var ser1=sheet.getRange("Q9");
var ser2=sheet.getRange("Q10");
var ser3=sheet.getRange("Q11");
var ser4=sheet.getRange("Q12");
var uvser=sheet.getRange("Q13");
var lcd1=sheet.getRange("R9");
var lcd2=sheet.getRange("R10");
var lcd3=sheet.getRange("R11");
var lcd4=sheet.getRange("R12");
var uvlcdr=sheet.getRange("R13");
var wellloc=sheet.getRange("K14");
var wellyear=sheet.getRange("K15");
var welldepth =sheet.getRange("M15");
var wellGPM =sheet.getRange("O15");
var wellcasing =sheet.getRange("Q15");
var pumpdate=sheet.getRange("K16");
var pumpsize =sheet.getRange("M16");
var presstankdate =sheet.getRange("O16");
var presstanksize =sheet.getRange("Q16");
var systemnotes=sheet.getRange("K17");
var iron=sheet.getRange("J19");
var manganese=sheet.getRange("J20");
var pH=sheet.getRange("J21");
var hardness=sheet.getRange("J22");
var arsenic=sheet.getRange("L19");
var radon=sheet.getRange("L20");
var uranium=sheet.getRange("L21");
var lead=sheet.getRange("L22");
var chloride=sheet.getRange("N19");
var nitrate=sheet.getRange("N20");
var fluoride=sheet.getRange("N21");
var bacteria=sheet.getRange("N22");
var tannins=sheet.getRange("P19");
var other=sheet.getRange("P20");
var smell=sheet.getRange("P21");
var citywater=sheet.getRange("P22");
var labtestfreq=sheet.getRange("K23");
var lastlabdate=sheet.getRange("O23");
var nextlabdate=sheet.getRange("Q23");
var saltdelfreq=sheet.getRange("K24");
var saltqty=sheet.getRange("M24");
var lastsaltdate=sheet.getRange("O24");
var nextsaltdate=sheet.getRange("Q21");
var callaheadbox=sheet.getRange("S24");

var size1match="=if(H7>0, filter('Sample Data'!$AZ$2:$AZ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var size2match="=if(H7>0, filter('Sample Data'!$BE$2:$BE$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var size3match="=if(H7>0, filter('Sample Data'!$BJ$2:$BJ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var size4match= "=if(H7>0, filter('Sample Data'!$BO$2:$BO$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var st1match="=if(H7>0, filter('Sample Data'!$BA$2:$BA$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var st2match="=if(H7>0, filter('Sample Data'!$BF$2:$BF$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var st3match="=if(H7>0, filter('Sample Data'!$BK$2:$BK$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var st4match="=if(H7>0, filter('Sample Data'!$BP$2:$BP$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var uvbulbmatch="=if(H7>0, filter('Sample Data'!$BU$2:$BU$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ht1match="=if(H7>0, filter('Sample Data'!$BB$2:$BB$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ht2match="=if(H7>0, filter('Sample Data'!$BG$2:$BG$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ht3match="=if(H7>0, filter('Sample Data'!$BL$2:$BL$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ht4match="=if(H7>0, filter('Sample Data'!$BQ$2:$BQ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var mod1match="=if(H7>0, filter('Sample Data'!$BC$2:$BC$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var mod2match="=if(H7>0, filter('Sample Data'!$BH$2:$BH$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var mod3match="=if(H7>0, filter('Sample Data'!$BM$2:$BM$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var mod4match="=if(H7>0, filter('Sample Data'!$BR$2:$BR$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var uvmodmatch="=if(H7>0, filter('Sample Data'!$BW$2:$BW$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ser1match="=if(H7>0, filter('Sample Data'!$BD$2:$BD$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ser2match="=if(H7>0, filter('Sample Data'!$BI$2:$BI$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ser3match="=if(H7>0, filter('Sample Data'!$BN$2:$BN$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ser4match="=if(H7>0, filter('Sample Data'!$BS$2:$BS$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var uvsermatch="=if(H7>0, filter('Sample Data'!$BX$2:$BX$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var lcd1match="=if(H7>0, filter('Sample Data'!$EA$2:$EA$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var lcd2match="=if(H7>0, filter('Sample Data'!$EB$2:$EB$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var lcd3match="=if(H7>0, filter('Sample Data'!$EC$2:$EC$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var lcd4match="=if(H7>0, filter('Sample Data'!$ED$2:$ED$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var uvlcdrmatch="=if(H7>0, filter('Sample Data'!$EE$2:$EE$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var welllocmatch="=if(H7>0, filter('Sample Data'!$BY$2:$BY$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var wellyearmatch="=if(H7>0, filter('Sample Data'!$BZ$2:$BZ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var welldepthmatch ="=if(H7>0, filter('Sample Data'!$CA$2:$CA$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var wellGPMmatch ="=if(H7>0, filter('Sample Data'!$CB$2:$CB$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var wellcasingmatch ="=if(H7>0, filter('Sample Data'!$CC$2:$CC$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var pumpdatematch="=if(H7>0, filter('Sample Data'!$CD$2:$CD$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var pumpsizematch ="=if(H7>0, filter('Sample Data'!$CE$2:$CE$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var presstankdatematch ="=if(H7>0, filter('Sample Data'!$CF$2:$CF$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var presstanksizematch ="=if(H7>0, filter('Sample Data'!$CG$2:$CG$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var systemnotesmatch="=if(H7>0, filter('Sample Data'!$CH$2:$CH$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var ironmatch="=if(H7>0, filter('Sample Data'!$CP$2:$CP$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var manganesematch="=if(H7>0, filter('Sample Data'!$CQ$2:$CQ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var pHmatch="=if(H7>0, filter('Sample Data'!$CR$2:$CR$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var  hardnessmatch="=if(H7>0, filter('Sample Data'!$CS$2:$CS$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var arsenicmatch="=if(H7>0, filter('Sample Data'!$CJ$2:$CJ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var radonmatch="=if(H7>0, filter('Sample Data'!$CI$2:$CI$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var uraniummatch="=if(H7>0, filter('Sample Data'!$CK$2:$CK$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var leadmatch="=if(H7>0, filter('Sample Data'!$CN$2:$CN$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var chloridematch="=if(H7>0, filter('Sample Data'!$CT$2:$CT$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var nitratematch="=if(H7>0, filter('Sample Data'!$CM$2:$CM$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var fluoridematch="=if(H7>0, filter('Sample Data'!$CW$2:$CW$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var bacteriamatch="=if(H7>0, filter('Sample Data'!$CL$2:$CL$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var tanninsmatch="=if(H7>0, filter('Sample Data'!$EH$2:$EH$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var othermatch="=if(H7>0, filter('Sample Data'!$CO$2:$CO$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var smellmatch="=if(H7>0, filter('Sample Data'!$CU$2:$CU$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var citywatermatch="=if(H7>0, filter('Sample Data'!$CV$2:$CV$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var labtestfreqmatch="=if(H7>0, filter('Sample Data'!$DV$2:$DV$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var lastlabdatematch="=if(H7>0, filter('Sample Data'!$DW$2:$DW$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var saltdelfreqmatch="=if(H7>0, filter('Sample Data'!$AL$2:$AL$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var saltqtymatch="=if(H7>0, filter('Sample Data'!$AM$2:$AM$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var lastsaltdatematch="=if(H7>0, filter('Sample Data'!$AJ$2:$AJ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";
var callaheadboxmatch="=if(H7>0, filter('Sample Data'!$DZ$2:$DZ$8499,'Sample Data'!$A$2:$A$8499=$H$7),\"\")";

custid.clearContent();
contractrenewal.clearContent();
name.clearContent();
prevcust.clearContent();
address.clearContent();
zip.clearContent();
city.clearContent();
source.clearContent();
phone.clearContent();
altphone.clearContent();
email.clearContent();
altemail.clearContent();
altcontact.clearContent();
type.clearContent();
contactphone1.clearContent();
contactphone2.clearContent();
contactemail.clearContent();
mailbox.clearContent();
ccbox.clearContent();
emailbillbox.clearContent();
mailingaddress.clearContent();
startdate.clearContent();
wintermailaddress.clearContent();
enddate.clearContent();
keycode.clearContent();
directions.clearContent();

address.setDataValidation(valaddress);
name.setDataValidation(valname);

custid.setFormula(custidmatch);
contractrenewal.setFormula(contractrenewdatematch);
prevcust.setFormula(prevcustmatch);
zip.setFormula(zipmatch);
city.setFormula(citymatch);
source.setFormula(sourcematch);
phone.setFormula(phonematch);
altphone.setFormula(altphonematch);  
email.setFormula(emailmatch);
altemail.setFormula(altemailmatch);
altcontact.setFormula(altcontactmatch);
type.setFormula(typematch);
contactphone1.setFormula(contactphone1match);
contactphone2.setFormula(contactphone2match);
contactemail.setFormula(contactemailmatch);
mailbox.setFormula(mailboxmatch);
ccbox.setFormula(ccboxmatch);
emailbillbox.setFormula(emailbillsboxmatch);
mailingaddress.setFormula(mailaddressmatch);
wintermailaddress.setFormula(wintermailaddressmatch);
keycode.setFormula(keycodematch);
startdate.setFormula(startdatematch);
enddate.setFormula(enddatematch);
directions.setFormula(directionsmatch);
size1.setFormula(size1match);
size2.setFormula(size2match);
size3.setFormula(size3match);
size4.setFormula(size4match);
st1.setFormula(st1match);
st2.setFormula(st2match);
st3.setFormula(st3match);
st4.setFormula(st4match);
uvbulb.setFormula(uvbulbmatch);
ht1.setFormula(ht1match);
ht2.setFormula(ht2match);
ht3.setFormula(ht3match);
ht4.setFormula(ht4match);
mod1.setFormula(mod1match);
mod2.setFormula(mod2match);
mod3.setFormula(mod3match);
mod4.setFormula(mod4match);
uvmod.setFormula(uvmodmatch);
ser1.setFormula(ser1match);
ser2.setFormula(ser2match);
ser3.setFormula(ser3match);
ser4.setFormula(ser4match);
uvser.setFormula(uvsermatch);
lcd1.setFormula(lcd1match);
lcd2.setFormula(lcd2match);
lcd3.setFormula(lcd3match);
lcd4.setFormula(lcd4match);
uvlcdr.setFormula(uvlcdrmatch);
wellloc.setFormula(welllocmatch);
wellyear.setFormula(wellyearmatch);
welldepth.setFormula(welldepthmatch);
wellGPM.setFormula(wellGPMmatch);
wellcasing.setFormula(wellcasingmatch);
pumpdate.setFormula(pumpdatematch);
pumpsize.setFormula(pumpsizematch);
presstankdate.setFormula(presstankdatematch);
presstanksize.setFormula(presstanksizematch);
systemnotes.setFormula(systemnotesmatch);
iron.setFormula(ironmatch);
manganese.setFormula(manganesematch);
pH.setFormula(pHmatch);
hardness.setFormula(hardnessmatch);
arsenic.setFormula(arsenicmatch);
radon.setFormula(radonmatch);
uranium.setFormula(uraniummatch);
lead.setFormula(leadmatch);
chloride.setFormula(chloridematch);
nitrate.setFormula(nitratematch);
fluoride.setFormula(fluoridematch);
bacteria.setFormula(bacteriamatch);
tannins.setFormula(tanninsmatch);
other.setFormula(othermatch);
smell.setFormula(smellmatch);
citywater.setFormula(citywatermatch);
labtestfreq.setFormula(labtestfreqmatch);
saltdelfreq.setFormula(saltdelfreqmatch);
saltqty.setFormula(saltqtymatch);
lastlabdate.setFormula(lastlabdatematch);
lastsaltdate.setFormula(lastsaltdatematch);
callaheadbox.setFormula(callaheadboxmatch);

}

因此，我解决了许多格式问题，并相信未找到参考与复制数据时的格式有关。我对工作表中的所有单元格应用了相同的格式，似乎已经解决了问题。感谢库珀的投入。