Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / #N/A Error in excel fr...
Microsoft Dynamics NAV (Archived)

#N/A Error in excel from export using Configuration Package NAV 2013 R2

(0) ShareShare
ReportReport
Posted on by Cathy Burn 65

I am wanting to update the email addresses in the Customer Table.  I have exported the Customer table using the configuration package into excel.  I have copied the result into a new excel spreadsheet (S1) using , Paste, Values.

I am using vlookup to a separate spreadsheet (S2) to populate the email address column in (S1)

The result is #N/A in the email field (S1)

Both spreadsheets are formatted as general and are sorted by column A

I have tested the vlookup function between worksheets within (S2) and it works, but the same test within (S1) returns the #N/A error.

Has anyone resolved this error please.

*This post is locked for comments

  • Suggested answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: #N/A Error in excel from export using Configuration Package NAV 2013 R2

    hi

    why do you use vlookup to get the values from the email column.

    best way is simple: Table_S2_Cell1 = Table_S1_EmailCell

    then copy this down the column in table s2.

    for function vlookup check:

    blogs.office.com/.../solutions-to-three-common-problems-when-using-vlookup

    stackoverflow.com/.../excel-failing-at-string-vlookup

    stackoverflow.com/.../vlookup-text-inside-a-string

    best regards

  • Cathy Burn Profile Picture
    Cathy Burn 65 on at
    RE: #N/A Error in excel from export using Configuration Package NAV 2013 R2

    Hi Tobias - further progress.  I have now managed to import my file.

    I used the original export excel file.  VLOOKUP would not work if I selected the fields using the function wizard.  I had to manually enter the syntax.

    I also had to use Column B for the Lookup_value.

    This is not the best option as in my case the field contained the customers name, the export file and the lookup spreadsheet were from different sources and may not have been identical.

    Using column A which is the customer number and primary key would be a more accurate option.

    If you know how to use column A as the lookup_value in VLOOKUP, within the original exported excel file.  I would be most grateful.

    Regards

    Cathy

  • Cathy Burn Profile Picture
    Cathy Burn 65 on at
    RE: #N/A Error in excel from export using Configuration Package NAV 2013 R2

    Hi Tobias, thanks for your response.

    I have now been able to populate my email addresses into in S1 spreadsheet by VLOOKUP on Column 2, which is the Customer Name field.

    However I now get an error when trying to import the spreadsheet

    ' Excel does not contain XML mappings.'

    It would be more correct to use the original exported spreadsheet from NAV as this will contain the XML mapping, however the formatting of this spreadsheet is such that you cannot perform a VLOOKUP.

    for example looking up column 1 does not return the customer number but  [@[No.]]

    =VLOOKUP([@[No.]],'[Customer.xlsx]Sheet1'!$A$4:$C$7,2,false)

    [Customer.xlsx]Sheet1 - is S2

    $A$4:$C$7 - is the array

    Column 2 contains the email address

    We have re-installed NAV2013 in April, so am only just getting used to it.  Previously had V5.1 and used dataports.

    Am I using the correct method to update the email addresses in the customer table?

  • Tobias Strobelt Profile Picture
    Tobias Strobelt 152 on at
    RE: #N/A Error in excel from export using Configuration Package NAV 2013 R2

    Hi Cathy,

    can you please give an example of your VLOOKUP syntax and the column layout you have in your S2 spreadsheet? I´ve tried the same and I get the correct email results. My VLOOKUP looks as following:

    Spreadsheet S2:

    No.               Email
    01121212     spotsmeyer's.furnishings@cronuscorp.net
    ....

    =VLOOKUP(A2;Table35;52;FALSE)

    whereas Table35 would represent the full customer package table in S1 excluding columns and column 52 is the [email] column.

    Best regards,
    Tobias

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans