Returning a value of 1 of 3 cells
I have 3 columns. [REQ Amount], [PO Amount], [INV Amount]. I want another column [Amount] to be the most relevant "amount" . At first a REQ is done for say $2. Then a PO is sent for the said REQ but gets messed up and issued for say $3, then my actual cost might be $3. Then the invoice comes in at $2. So when the invoice is paid, I want the $2 to be my most relevant expense amount.
How do I write a statement that says if INV Amount and PO Amount are Null, then REQ Amount, Else if INV Amount is Null then PO Amount, else, INV Amount?
Thoughts?
Best Answer
-
Good Afternoon,
I want through your scenario above and believe an IF statement formula would solve your most relevant "amount" column.
This is the formula I wrote and the scenarios I tested are blow.
=IF([INV Amount]@row = [PO Amount]@row, [REQ Amount]@row, [INV Amount]@row)
Please let me know if this helped!
Answers
-
Good Afternoon,
I want through your scenario above and believe an IF statement formula would solve your most relevant "amount" column.
This is the formula I wrote and the scenarios I tested are blow.
=IF([INV Amount]@row = [PO Amount]@row, [REQ Amount]@row, [INV Amount]@row)
Please let me know if this helped!
-
Hi Kayla, good afternoon to you as well. This kinda works. In the end, the most relevant number is the later one in the process. So I'm encumbering with a REQ $2 from a budget, until a PO is done, then the $2 is superseded by a $3 PO... until an invoice is received then the INV $2 supersedes the PO. Does that make more sense?
-
EUREKA.. i GOT IT! Thanks for the insights!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …