CODEWITHSUNDEEP
excelgoogle-sheetsformula
runner16
runner16

Reputation: 107

Curly Brackets added to formula in Excel workbook when downloading from a Google Sheet

I have a Google Sheet Workbook with the following formula:

=IFERROR(XLOOKUP(A8,'Budget Ids'!A2, 'Budget Ids'!C2), "")

The above formula works in the Google Sheet. When I download the Google Sheet as a Microsoft Excel workbook and then click "Enable Editing", I get this:

{=IFERROR(XLOOKUP(A9,'Budget Ids'!A3, 'Budget Ids'!C3), "")}

It wraps my entire formula in curly brackets. If I click into the cell, the brackets go away and the value from my formula appears. But I don't understand why the curly brackets are being added when I download it as a Microsoft Excel workbook and how to fix it as opposed to manually clicking into the cell. As far as I know, IFERROR and XLOOKUP are valid functions in both Google Sheets and Microsoft Excel.

Any help would be greatly appreciated.

Upvotes: 1

Views: 2431

Answers (3)

Ani
Ani

Reputation: 11

Just do a replace all for 'xlookup' with 'xlookup'... and all your #name errors will vanish. Excel away!

Upvotes: 1

Taras Panasiuk
Taras Panasiuk

Reputation: 1

This VBA macro transforms all array formulas into standard formulas across all sheets, essentially simulating a refresh of cell (F2 + Enter).

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ' Turn off Alert Message "You can't change the part of array"
    Application.DisplayAlerts = False
    
    ' Iterate through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Attempt to find all cells containing formulas in the worksheet
        On Error Resume Next
        Set arrayFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
              
        If Not arrayFormulas Is Nothing Then
            ' Process each area containing formulas
            For Each rng In arrayFormulas.Areas
                ' This is the fastest way to refresh arrays avoiding time-consuming checks
                ' Note: we can't refresh part of array because the alert will be triggered
                ' Thats why Application.DisplayAlerts = False
                On Error Resume Next
                ' Refresh array formulas by reapplying them
                ' This action imitates F2 + Enter
                rng.Formula2R1C1 = rng.Formula2R1C1
                On Error GoTo 0
            Next rng

        End If
        ws.UsedRange.EntireColumn.AutoFit
        
    
    Next ws

Upvotes: 0

Andy Brown
Andy Brown

Reputation: 5522

What a weird problem this is. A VBA solution is to select one of the cells in the block and run this VBA macro:

Sub GetRidOfStupidBrackets()

'this assumes you have one of the cells in the block selected
ActiveCell.CurrentRegion.Select
Selection.Value = Selection.FormulaR1C1

End Sub

This is like clicking on each cell, pressing F2 to edit it and then pressing ENTER (which solves the problem).

Upvotes: 0

Related Questions