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!
Answers
-
Hey @Haley Gehris
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!