#N/A error with the VLOOKUP function
Béa annecy
Posted messages
70
Status
Membre
-
yclik Posted messages 3866 Registration date Status Membre Last intervention -
yclik Posted messages 3866 Registration date Status Membre Last intervention -
Hello
I have a first Calc file in which I record my income.
Field #1: date
Field #4: client
Field #5: invoice number in the format 2014-0083 for example.
I have a second Calc file to create my invoices.
When I enter the invoice number in the "invoice" file, I would like the date and the client's name to be automatically generated using the VLOOKUP function.
However, I am getting #N/A in both fields.
Calc is therefore unable to find the occurrence 2014-0083.
Maybe it's a format issue (text on one side / numeric on the other)? I don't know how to check if a data is in text or numeric format.
Thank you for your ideas and your help.
I have a first Calc file in which I record my income.
Field #1: date
Field #4: client
Field #5: invoice number in the format 2014-0083 for example.
I have a second Calc file to create my invoices.
When I enter the invoice number in the "invoice" file, I would like the date and the client's name to be automatically generated using the VLOOKUP function.
However, I am getting #N/A in both fields.
Calc is therefore unable to find the occurrence 2014-0083.
Maybe it's a format issue (text on one side / numeric on the other)? I don't know how to check if a data is in text or numeric format.
Thank you for your ideas and your help.
2 réponses
Hello
If the sheets are in two different workbooks, it's not easy to manage, as it requires using the DDE function (see help).
A proposed solution
In the "invoice" workbook:
1-Create a sheet "recipe_copy" in the "invoice" workbook that will be a copy of the recipe workbook
2-Copy the source table (recipe) into the target workbook (invoice, sheet "recipe_copy" by Edit > Paste Special > Link
In the "invoice" workbook
Build the VLOOKUP formula provided that the invoice number is the 1st column of the data
So copy/paste with a link in the 1st column the invoice numbers and for the following columns copy/paste with a link the other data
If the sheets are in two different workbooks, it's not easy to manage, as it requires using the DDE function (see help).
A proposed solution
In the "invoice" workbook:
1-Create a sheet "recipe_copy" in the "invoice" workbook that will be a copy of the recipe workbook
2-Copy the source table (recipe) into the target workbook (invoice, sheet "recipe_copy" by Edit > Paste Special > Link
In the "invoice" workbook
Build the VLOOKUP formula provided that the invoice number is the 1st column of the data
So copy/paste with a link in the 1st column the invoice numbers and for the following columns copy/paste with a link the other data
