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

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.