Apply part of invoice to different object codes

Options

Hello, I am trying to write a formula to give me the amount of an invoice I need to apply to 2 different object codes. GL code 6022 needs to be used for payments from \$100,000-\$125,000 and the other GL code 6021 is used for everything else. I have a current invoice column and a historical invoice column. So for example:

Current invoice is: \$120,000

Historically invoiced: \$80,000

So I need to pay out \$40k, but the first \$20k needs to be applied to the GL code 6021 and the second \$20k needs to be applied to the GL code 6022.

I'd like to create 2 new columns, 1 for the 6022 GL and one for the 6021 GL and have a formula calculating the amount of the invoice to apply to each. TIA for your help with this!

• ✭✭✭✭✭✭
Options

Try this

GL code 6022

=IF(AND([Current invoice]@row >= 100000, [Current invoice]@row <= 125000), IF([Current invoice]@row > [Historically invoiced]@row, IF([Current invoice]@row - [Historically invoiced]@row > [Current invoice]@row - 100000, ([Current invoice]@row - [Historically invoiced]@row) - ([Current invoice]@row - 100000), [Current invoice]@row - [Historically invoiced]@row)))

GL code 6021

=IF(AND([Current invoice]@row >= 100000, [Current invoice]@row <= 125000, [Current invoice]@row > [Historically invoiced]@row), ([Current invoice]@row - [Historically invoiced]@row) - [GL code 6021]@row, IF(AND([Current invoice]@row > [Historically invoiced]@row, OR([Current invoice]@row < 100000, [Current invoice]@row > 125000)), [Current invoice]@row - [Historically invoiced]@row))

I wasn't quite sure what to do if the Historically invoiced amount was larger than the Current Invoice. You will see within the IF criteria I check for that, and don't allow a value to show up in either 6021 or 6022.

Will this work for you? If the logic isn't right, shout out to me.

Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!