If, Then Formula

lisarae723

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

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.