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

Formula As Date Changes.png

Find more posts tagged with

Sales

Manufacturing

Sheets

Formulas

Reports

Services

Marketing

Support

IT & Operations

Product Development

Project Management

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…