Formula error in link to another spreadsheet

New Contributor

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? 

8 Replies

Hi @Gary_Bostock,

Can you copy your formula here? It could help figuring out what your issue is.


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?

@Sergei Baklan 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.

@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





It seems like a simple vlookup would do the trick here.

=VLOOKUP(A47,'[LISTING 2020.xlsx]Sheet1'!$A:$B,2,0)

Change "sheet1" for the sheet name where your data is.

Change "$A:$B" for where your table is.

This reads: "Find the value located in A47 within column A of Listing 2020.xlsx, sheet1. Then return the value located right next to it (in column B).

I hope this helps.


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)

@Sergei Baklan 


So I use the tool to do the formula



And the result is


=VLOOKUP(A14.'[LISTINGS 2020.xlsx]TIMBER'!$A$7:$A$147.'[LISTINGS 2020.xlsx]TIMBER'!$B$7:$B$147) which still throws up the same error.


Is there not perhaps some keyboard or numeric setting in my program options, I really don't see how the template provided can go wrong?

I have setup 2 clean spreadsheet to test the formula basically trying to do the same thing, VLOOKUP between spreadsheets and I am getting the same error.


@Gary_Bostock If you provide your test spreadsheets it will be easier to explain what's wrong exactly. In any case formula in your post won't work

=VLOOKUP(A14.   <= here shall be separator which is
                   comma or semicoln bu practically never dot
'[LISTINGS 2020.xlsx]TIMBER'!$A$7:$A$147.
                 <= the same (dot) and you take onlu one column
'[LISTINGS 2020.xlsx]TIMBER'!$B$7:$B$147)
                 <= for VLOOKUP here shall be column number, 
                    not the reference on the column.
Except dots that's XLOOKUP formula structure, not VLOOKUP.