Forum Discussion

Lisa_BBP's avatar
Lisa_BBP
Trusted Cover User
4 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
    4 months 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.

Mike_James's avatar
Mike_James
Ultimate Cover User
3 months ago

Hi Lisa_BBP , @Nigel_ST , in addition I found another method - highlight the debit and credit columns  (one column at a time), excluding any merged cells, and select Data, Text to columns. This also clears the zero-length strings.

Looking for something else?

Search the Community Forum for answers or find your topic and get the conversation started!

Community home

Dig into MYOB Academy for free courses, learning paths and live events to help build your business with MYOB.