Hi:
I have five companies in Business Central, and each tab of my Excel workbook shows five tabs.
I had to format the natural account number column to be number instead of text. I did this through Copy and Paste Special.
Two of the five companies (tabs) do not like the fact that I formatted the natural account number column to be number instead of text. They gave me the "#VALUE#" error.
Why?
Thanks!
When you highlight the cell with a #VALUE and click the Check Error button, what is the message that displayed?
No error is given.
That typically means it is an Excel issue, not a Jet issue. This may point to the SUMIF where some of the values are as text and others as numbers so it can't resolve the different value types.
FYI, i think I reached out to you on LinkedIn (at least I took a guess based on name and role). Happy to help offline
Hi Bob:
Thank you, for offering to help.
Were you going to charge a lot for your services. :(
John
Hi Bob:
I figured out, just now, why the error is occurring on at least one of the companies.
That company does not have values for the time period in which I'm running the report.
So, since I'm changing the natural account number column to be number instead of text, Excel is saying that the blank cannot have a 0 added to it through Paste Special.
John
That's great! It is tough because #VALUE can be generated by Jet or by Excel. If it is Jet generated, the Check Error will will return an error. If an error is not returned, that is an Excel issue. Glad you were able to get to the bottom of it.
For most end user, is it acceptable to have this appear like this?
Not sure if there was supposed to be a picture to show "appear like this".
Ideally, to avoid issues like this, it would be best to have dynamic reports that will only create sheets or list accounts where there is activity for the filters. This definitely takes some planning but can improve performance and reduce errors.
Any time you end up with empty values, it can cause issues with both Jet and Excel functions.
Thanks, BobRoss! Your tip on hiding the sheet dynamically solved the problem! Again, thank you!