If, Then Formula
Hi everyone,
I've searched through the community but couldn't find the right solution.
I need to feed specific sales metrics into a dashboard but want it to update, according to the Today column. I receive the previous months information on the 15th of each month, so the formula would need to cover a 45 day period. I added a date column with each row containing the 1st of each month. (row 1 = 01/01/19, row2 = 02/01/19 etc.). Any suggestions would be greatly appreciated:)
For example:
If today's date is June 12th, I need it to find the cell with the dollar amount entered on 04/01/19 in the column [Prev 12 MO SALES ANNUALIZED] shown as $2,624,078.62.
When today's date changes to June 15th, it will need to l find the cell with the dollar amount entered on 05/01/19 from the column [Prev 12 MO SALES ANNUALIZED] shown as $2375,415.93.
See screen shot
Comments
-
L_123 ✭✭✭✭✭✭
so you are saying something like
=index([Prev 12 MO SALES ANNUALIZED]:[Prev 12 MO SALES ANNUALIZED],match(if(day(today())<15, date(year(today()),month(today())-2,1),date(year(today()),month(today())-1,1),Date:Date,0))
I'm not sure i'm understanding this completely, and this might bring up a circular reference. Try this and see if it produces what you want. If it doesn't work can you tell us a little more about how the numbers are generated and organized on the sheet?
-
lisarae723 ✭✭✭✭✭
Im getting an error msg "incorrect argument". My apologies if I didn't explain it properly.
The sheet is updated mid-month with data from the previous month. Right now my dashboard is pulling the the cell info from the month of April.
May 15th to June 14th, I should pull that dollar amount entered on 04/01/19.
June 15th to July 14th , I should pull that dollar amount entered on 05/01/19.
July 15th to August 14th, I should pull that dollar amount entered on 06/01/19.
August 15th to September 14th, I should pull that dollar amount entered on 07/01/19....and so on.
-
L_123 ✭✭✭✭✭✭
Apologies, I had a typo in there.
=INDEX([Prev 12 MO SALES ANNUALIZED]:[Prev 12 MO SALES ANNUALIZED], MATCH(IF(VALUE(DAY(TODAY())) < 15, DATE(YEAR(TODAY()), VALUE(MONTH(TODAY())) - 2, 1), DATE(YEAR(TODAY()), VALUE(MONTH(TODAY())) - 1, 1)), Date:Date, 0))
-
lisarae723 ✭✭✭✭✭
It works! You saved me so much time. I really appreciate your help.
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/50511/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":50,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
