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 to a trial balance exported to excel? Why have you removed this simple yet handy feature?

Frustrated Partner

MYOB is getting worse!

  • 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.

10 Replies

Sort By
  • 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.

    • 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.

    • Mike_James's avatar
      Mike_James
      Ultimate Partner
      11 days 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.

  • Earl_HD's avatar
    Earl_HD
    MYOB Moderator
    2 months ago

    Hi Lisa_BBP,

    Thanks for your post. I recommend visiting these related posts, which you might find helpful. 
    Export to Excel doesn't work
    Exporting Reports to Excel not working

    If you're still experiencing the issue after reviewing those posts, why not try our virtual assistant, MOCA? It's designed to dive deeper into issues like this. Give it a go! Feel free to engage with MOCA through myob.com/support for any queries or assistance you might need. If MOCA can't provide the help you need, our live chat team is available to pick up where MOCA left off.

    Regards,
    Earl

    • Lisa_BBP's avatar
      Lisa_BBP
      Trusted Partner
      2 months ago

      Thanks for your reply, Earl, however you did not assist appropriately on this occasion as referring me to a "bot" clearly demonstrates that you are not interested in resolving this matter. I'm sure your time is valuable so I will continue to solve the matter in my own time.

      Please do not mark this as a solution to the question, instead I suggest you archive it with the many other unresolved queries.

  • Mike_James's avatar
    Mike_James
    Ultimate Partner
    2 months ago

    Hi Lisa_BBP , would the formula you are adding be a vlookup or similar? Sometimes the looked-up value is not exactly the same as the value in the lookup range, which you can test by entering a formula like =a1=a23. If this returns false, then the values are somehow different.

    What values result in the error?

     

    • Lisa_BBP's avatar
      Lisa_BBP
      Trusted Partner
      2 months ago

      Hi Mike thank you for your reply. The formula is a very simple formula ie =a2-a1 using the amounts from the trial balance that have been exported to excel. I use the exported trial balance and enter the accountant adjustments and create the simple subtraction formula to calculate the difference in order to enter a manual adjustment journal to reconcile month end amounts in MYOB. The formula always gives me a #VALUE and it seems that the amounts in the downloaded trial balance are text and not numbers. My own solution is to enter the trial balance figures in separate columns in order to calculate the difference. This is not ideal as it is double entering values, hence doubling my data entry time. Does MYOB export the trial balance amounts as text and not numbers?

      • Mike_James's avatar
        Mike_James
        Ultimate Partner
        2 months ago

        Hi Lisa_BBP , the error is caused by the empty cells. They appear to be empty but might not be. If you edit one of those cells, then backspace (either in the cell or in the formula bar), then press Enter to update, the formula will work. So there must be a non-printable character in those cells. 

        I haven't found any other way to correct those cells; maybe someone else will have a suggestion.