View original (French)

#N/A error with the VLOOKUP function

Béa annecy Posted messages 70 Status Member -  
yclik Posted messages 3875 Registration date   Status Member 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.

2 answers

  1.
    yclik Posted messages 3875 Registration date   Status Member Last intervention   1 611
     
    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

    1