Forum Discussion

Lisa_BBP's avatar
Lisa_BBP
Trusted Partner
2 months ago

Excel formula not working when exported

Hello MYOB When I export a trial balance to excel I used to be able to add a formula but now it's showing a VALUE error. What has changed in the background that I am no longer able to add a formula ...
  • Nigel_ST's avatar
    Nigel_ST
    13 days ago

    Hi Lisa_BBP,
    This is caused by the MYOB export putting something in cells that appear blank, that isn't a number. Using a formula that references that cell and expects a number, will result in the #VALUE! error.

    This issue is visible when you look at the name of an account on export and its truncated, but when you press delete on the cell to the right, the full name of the account becomes visible. Its also identifiable when you 'ctrl+arrow' through a document which should take you to the next cell with data, but it skips whole sections with or without numbers as excel considers the entire area to contain information, like a whole column of DR despite only several rows containing actual numbers. This issued does not exist on Excel exports from the desktop AR package, just the cloud version of MYOB.

    The Export process needs to be corrected to not export zero length strings.

     

    This is solvable in a roundabout way:

    1. Open find and replace (Ctrl+H)
    2. Leave the "Find What" field blank
    3. Enter "x" in the "Replace With" field
    4. Make sure to check the "Match Entire Cell Contents" box
    5. Click Replace All
    6. Now switch what you entered in each field. Put the "x" in Find what, and delete everything in Replace with
    7. Click Replace All
    8. All apparently empty cells should now be truly empty.

    Yep, MYOB needs to improve their exporting processes.

Nigel_ST's avatar
Nigel_ST

Hi Lisa_BBP,
This is caused by the MYOB export putting something in cells that appear blank, that isn't a number. Using a formula that references that cell and expects a number, will result in the #VALUE! error.

This issue is visible when you look at the name of an account on export and its truncated, but when you press delete on the cell to the right, the full name of the account becomes visible. Its also identifiable when you 'ctrl+arrow' through a document which should take you to the next cell with data, but it skips whole sections with or without numbers as excel considers the entire area to contain information, like a whole column of DR despite only several rows containing actual numbers. This issued does not exist on Excel exports from the desktop AR package, just the cloud version of MYOB.

The Export process needs to be corrected to not export zero length strings.

 

This is solvable in a roundabout way:

  1. Open find and replace (Ctrl+H)
  2. Leave the "Find What" field blank
  3. Enter "x" in the "Replace With" field
  4. Make sure to check the "Match Entire Cell Contents" box
  5. Click Replace All
  6. Now switch what you entered in each field. Put the "x" in Find what, and delete everything in Replace with
  7. Click Replace All
  8. All apparently empty cells should now be truly empty.

Yep, MYOB needs to improve their exporting processes.

Lisa_BBP's avatar
Lisa_BBP
Trusted Partner
11 days ago

Hi Nigel_ST

Thank you so much for your assistance. MYOB certainly makes the simplest tasks more difficult these days.