If, Then Formula

lisarae723
lisarae723 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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

Formula As Date Changes.png

Comments

  • L_123
    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
    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
    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
    lisarae723 ✭✭✭✭✭

    It works!  You saved me so much time. I really appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!