Forum Discussion

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

Formulas and Functions

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

Diamond Contributor

Nov 25, 2020

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)

Gary_Bostock
Copper Contributor
Dec 02, 2020
SergeiBaklan
Bennadeau
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.
- SergeiBaklan
  Diamond Contributor
  Dec 02, 2020
  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.

Resources