Hi,
I'm using excel 2013 (win7) to compile a file exported from SAP as excel 2007 (xlsx)

I converted a range in table (listobject)

I added some columns to the table (listobject) and filled those with some formulas

now I got some new hidden range names:
_xlfn.COUNTIFS
_xlfn.IFERROR
_xlfn.SUMIFS
(actually I'm using the new workbook functions COUNTIFS, IFERROR and SUMIFS)

all the ranges refers to #NAME? (some to #NOME? ... because I'm using the italian language)

I tried to rename, re-refer or delete the names with macros, no way!

I'could even leave the ranges, but I can't because it made me unable to use the table as source for a new Pivot table ... it gives me an error saying that it's impossible to get data from my table...I think that the error (#NAME) on the reference of the hidden names results in a reference error in my table.

The macro added the new formulas using the "formulaLocal" method, so I could write it in Italian, .

The macro finds the header name of each column in my table, then fill the formula and formats the cells.

I was triyng different combinations of formulas (english native or italian local) and different properties (Formula, FormulaLocal, FormulaR1C1, Formula R1C1Local) ...
I Tried the English sintax ("" + ,) with the property Formula or FormulaR1C1... still get the error
I Tried the European sintax (Italian Language + "" + ; ) with the property FormulaLocal or FormulaR1C1Local... still get the error


here is part of the code:
...
Code:
Application.Calculation = xlAutomatic       '<<< ripristina il calcolo automatico
   Application.ErrorCheckingOptions.BackgroundChecking = False
    
    For Each c In objTable.ListColumns
        'c.Select
        MyT = c.Name
        strColName = MyT
        Select Case MyT
            Case "destinee"

                strFormula = "SE(O([@[Customer Document Code]]=""DOCUMENTO INTERNO"";SE.ERRORE(TROVA(""-"";[@[Doc Rev]])>0;FALSO));""Internal"";""customer"")"
                strColFormat = "General"
                
            Case "External"
            
                strFormula = "SE([@[Customer Document Code]]=""DOCUMENTO INTERNO"";"""";SE.ERRORE(SE(TROVA(""-"";[@[Doc Rev]])>0;STRINGA.ESTRAI([@[Doc Rev]];1;TROVA("" - "";[@[Doc Rev]])-1));[@[Doc Rev]]))"

                strColFormat = "0"
                
            Case "Internal"
            
                strFormula = "SE([@[Customer Document Code]]=""DOCUMENTO INTERNO"";SE([@[Doc Rev]]="""";CONTA.PIŁ.SE([Fores Doc Code];[@[Fores Doc Code]];[Customer Document Code];[@[Customer Document Code]];[Doc Rev];""<>""&"""";[destinee];""internal"")+1;SCARTO(TabREV['#];CONFRONTA([@[Doc Rev]];SE(CONTA.SE(TabREV[numeri];[@[Doc Rev]])>0;TabREV[numeri];TabREV[lettere]);0)-1;0;1;1));SE.ERRORE(SE(TROVA(""-"";[@[Doc Rev]])>0;SCARTO(TabREV['#];CONFRONTA(STRINGA.ESTRAI([@[Doc Rev]];TROVA(""-"";[@[Doc Rev]])+1;LUNGHEZZA([@[Doc Rev]])-TROVA(""-"";[@[Doc Rev]]));SE(CONTA.SE(TabREV[numeri];STRINGA.ESTRAI([@[Doc Rev]];TROVA(""-"";[@[Doc Rev]])+1;LUNGHEZZA([@[Doc Rev]])-TROVA(""-"";[@[Doc Rev]])))>0;TabREV[numeri];TabREV[lettere]);0)-1;0;1;1));""""))"
                    
                strColFormat = "0"
                
            Case "Approval Within date"
            
                strFormula = "SE(E([@[Issue Date]=]=>0;[@destinee]=""customer"");[@[Issue Date]]+ApprovalLeadTime;"""")"

                strColFormat = "d/m/yyyy"
                
            Case "Late Approval"
            
                strFormula = "SE([@[Issue Date]=]=>0;SE(O([@[Approval Within date]=]=>OGGI();E([@[Approval Within date]=]=>[@[Response Date]];[@[Response Date]=]=>0));"""";SE([@[Response Date]=]=>0;[@[Response Date]];SE(CONTA.PIŁ.SE([Fores Doc Code];[@[Fores Doc Code]];[External];[@External]+1)>0;SE(SOMMA.PIŁ.SE([Issue Date];[Fores Doc Code];[@[Fores Doc Code]];[External];[@External]+1)>[@[Approval Within date]];SOMMA.PIŁ.SE([Issue Date];[Fores Doc Code];[@[Fores Doc Code]];[External];[@External]+1);[@[Approval Within date]]);OGGI()))-[@[Approval Within date]]);""n.a."")"
 
                strColFormat = "0"
                
            Case "Last Issue"
            
                strFormula = "SE([@[Doc Rev]]="""";""Last"";SE(CONTA.PIŁ.SE([Fores Doc Code];[@[Fores Doc Code]];[Issue Date];"">""&[@[Issue Date]])>0;"""";SE(CONTA.PIŁ.SE([Fores Doc Code];[@[Fores Doc Code]];[Issue Date];"""")>0;"""";""Last"")))"

                strColFormat = "General"
                
        End Select
        Select Case MyT
        
            Case "destinee", "External", "Internal", "Approval Within date", "Late Approval", "Last Issue"
                strFormula = "=" & strFormula
                
                With objTable.ListColumns(strColName).DataBodyRange
                    .NumberFormat = strColFormat
 '                   .Formula = strFormula
                   .FormulaR1C1Local = strFormula
                End With
                
        End Select
    Next c
    
    Application.Calculation = xlManual     '<<< disabilita il calcolo automatico (velocizza la macro)
    
    Application.ErrorCheckingOptions.BackgroundChecking = True
Any Suggestion?
Thanks

Dennis