Hello all,

Recently, we upgraded a client from 4.5 S/I to 2015.

I noticed an issue with several of the formulas and narrowed it down to the GLlink argument.

The below produces an "Incorrect Mathematical Expression" error:

GLActual("50000-100 to 50499-100, 50600-100 to 57499-100, 57510-100, 58000-100 to 59999-100",$B$4,$D$4)

However, if the GLlink argument only specifies accounts where there is a single range, it works:

GLActual( “50000-100 to 50499-100”,$B$4,$D$4)

GLActual( “50600-100 to 57499-100”,$B$4,$D$4)

GLActual(57510-100,$B$4,$D$4)

GLActual( “58000-100 to 59999-100”,$B$4,$D$4)

The client can't seem to combine the different ranges in one long string, they can only do them separately.

Is there a way to fix this?  I'm trying to avoid having to write some VBA to convert these formulas to a working version.

Note:  Ideally I would've preferred that the client use column A:A to indicate the GL accounts and simply reference it in the formula.  Unfortunately, since this report was created "pre-me" I was unable to provide that type of direction. 

Views: 56

Replies to This Discussion

What happens if you test putting the account string in Col A.

Same problem.

When do you get the error and can you post a screenshot of it?

=GLActual("50000-100 to 50499-100, 50600-100 to 57499-100, 57510-100, 58000-100 to 59999-100",$B$4,$D$4) returns a value of "Incorrect Mathematical Expression" in that cell.

Sage called me back. Apparently, the "," (comma) has been replaced with a "+" (plus) in this newer version of S/I.  

So now all the formulas would have to go from this:

=GLActual("50000-100 to 50499-100, 50600-100 to 57499-100, 57510-100, 58000-100 to 59999-100",$B$4,$D$4)

..to this:

=GLActual("50000-100 to 50499-100+ 50600-100 to 57499-100+ 57510-100+ 58000-100 to 59999-100",$B$4,$D$4)

How in the world would I even fix this?

My first thought is to do this through VBA but there are so many variables to consider:

How many "GL" formulas where accounts are specified could be affected (example GLActual, GLActualYTD, GLClosingBalance)?

How many of these formulas might have nested or mathematical additions or subtractinos to other GL formulas in the same cell?

This is a nightmare.  This client has 1,285 formulas in their S/I report (which could contain more than one iteration of a GL formula) which could contain these types of ranges.

Thoughts?

I use this AddIn and it works great.

The Find & Replace that comes with Excel is very limited.

This one is amazing.

You can find all the commas and replace them with the plus sign.

Advanced Find and Replace in Excel

Thanks Doug.  I'll try this out.

I'm pretty familiar with VBA, but I was having a hard time even comprehending how to write a Macro to fix this.  Nice to know there's a tool already out their.  Really appreciate it.

I have been using this AddIn for 5 years and love it.

Let me know what you think.

Maybe I'm using this wrong but I didn't see any option to replace the commas in a specific argument of a formula, Excel's Find & Replace already allows me to look in Formulas and replace characters but it replaces all of them, I don't want that.  If I use this addin, it give's me a similar funcitonality and converts this

=GLActual("50000-100 to 50499-100, 50600-100 to 57499-100, 57510-100, 58000-100 to 59999-100",$B$4,$D$4)

...to this

=GLActual("50000-100 to 50499-100+ 50600-100 to 57499-100+ 57510-100+ 58000-100 to 59999-100"+$B$4+$D$4)

$B$4 and $D$4 are arguments in excel and have to be separated by a comma.  Am I missing something?

After it changes ,$B$4,$D$4 to +$B$4+$D$4

Run it again and change +$B$4+$D$4 to ,$B$4,$D$4

Unfortunately this isn't going to work.  I run the risk of replacing all the commas in all the formulas.  I have to consider the fact that the user may have nested formulas, which means that simply running the search & replace again for just those two arguments wouldn't be the end of it.  I'd have to keep scanning for new errors as a result of replacing commas in other parts of all the formulas.  This would also replace commas in standard excel formulas as well.

Thanks for suggesting that addin but without specifying the argument location in the replace, it could potentially do more harm than good.

I've already started writing a two part macro to resolve this:

1.  Locate all GL formulas with errors and copy them on to a new sheet.

2.  After manually modifying all the formulas to reflect the new operator (in the new sheet), run another macro which replaces the GL formulas with the new structure.

FYI, Excel already has the capability of looking in Formulas as oppose to Values and replacing the characters appropriately.  It also has the ability to do this for an entire workbook as oppose to just one sheet.  The only added capabilities that the addin seems to provide is the ability to search multiple workbooks and selectively change whichever cells the user wishes to modify.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service