Nov 20 2020 03:38 AM
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?
Nov 20 2020 06:39 AM
Hi @Gary_Bostock,
Can you copy your formula here? It could help figuring out what your issue is.
Nov 20 2020 08:20 AM
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?
Nov 25 2020 04:01 AM
@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.
Nov 25 2020 04:06 AM
@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
Nov 25 2020 06:09 AM
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.
Nov 25 2020 07:59 AM
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)
Dec 02 2020 02:06 AM
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.
Dec 02 2020 08:49 AM
@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.