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

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

Let me know what you think.

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

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.

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?