Formula error in link to another spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1917462%22%20slang%3D%22en-US%22%3EFormula%20error%20in%20link%20to%20another%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1917462%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20fetch%20data%20from%20one%20spreadsheet%20into%20another.%20no%20matter%20which%20lookup%20formula%20I%20use%2C%20I%20get%20an%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20problem%20with%20this%20formula.%3C%2FP%3E%3CP%3ENot%20trying%20to%20type%20a%20formula%3F%3C%2FP%3E%3CP%3EWhen%20the%20first%20character%20is%20an%20(%3D)%20or%20(-)%20sign%2C%20Excel%20thinks%20it%20is%20a%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20I%20am%20using%20the%20Formula%20ribbon%2C%20%22lookup%20%26amp%3B%20reference%22%20button%20function%2C%20it%20should%20work%20with%20the%20link%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1917462%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1918453%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20error%20in%20link%20to%20another%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1918453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876043%22%20target%3D%22_blank%22%3E%40Gary_Bostock%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20formulas%20do%20not%20work%20wit%20links%2C%20they%20work%20with%20values%20and%20references.%20It's%20not%20clear%20what%20you%20try%20to%20do%20-%20create%20a%20link%20on%20the%20cell%20in%20another%20workbook%3B%20or%20create%20formula%20which%20takes%20value%20from%20the%20cell%20in%20another%20workbook%20based%20on%20some%20parameters%20how%20to%20find%20such%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1918159%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20error%20in%20link%20to%20another%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1918159%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876043%22%20target%3D%22_blank%22%3E%40Gary_Bostock%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ECan%20you%20copy%20your%20formula%20here%3F%20It%20could%20help%20figuring%20out%20what%20your%20issue%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

Hi @Gary_Bostock,

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

Highlighted

@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?

Highlighted

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

Highlighted

@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

Highlighted

@Gary_Bostock 

 

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.

Highlighted

@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)
Highlighted

@Sergei Baklan 

@Bennadeau 

So I use the tool to do the formula

Gary_Bostock_0-1606903281510.jpeg

 

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.

 

Highlighted

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