Forum Discussion

Gary_Bostock's avatar
Gary_Bostock
Copper Contributor
Nov 20, 2020

Formula error in link to another spreadsheet

I am trying to fetch data from one spreadsheet into another. no matter which lookup formula I use, I get an error:

 

There is a problem with this formula.

Not trying to type a formula?

When the first character is an (=) or (-) sign, Excel thinks it is a formula.

 

Since I am using the Formula ribbon, "lookup & reference" button function, it should work with the link? 

excel
Formulas and Functions

8 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 20, 2020

    Gary_Bostock 

    In general formulas do not work wit links, they work with values and references. It's not clear what you try to do - create a link on the cell in another workbook; or create formula which takes value from the cell in another workbook based on some parameters how to find such cell?

    • Gary_Bostock's avatar
      Gary_Bostock
      Copper Contributor
      Nov 25, 2020

      SergeiBaklan I want to use a referenced cell value in spreadsheet 1 to find the cell content next to the corresponding value in a column in spreadsheet 2.

    • Gary_Bostock's avatar
      Gary_Bostock
      Copper Contributor
      Nov 25, 2020

      Bennadeau =XLOOKUP(A47.'LISTINGS 2020.xlsx'!Code.'LISTINGS 2020.xlsx'!Description)

       

      The formula is referencing cell A47 and is in a spreadsheet WCCOSTINGS, looking for source data contained in spreadsheet named LISTINGS 2020

       

      Thanks

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 25, 2020

        Gary_Bostock 

        Are you sure you have dot as separator, not comma? I mean

        =XLOOKUP(A47,'LISTINGS 2020.xlsx'!Code,'LISTINGS 2020.xlsx'!Description)

instead of
=XLOOKUP(A47.'LISTINGS 2020.xlsx'!Code.'LISTINGS 2020.xlsx'!Description)

Resources