# Apply part of invoice to different object codes

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!

• ✭✭✭✭✭✭

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!