Returning cell values based on date.

Krucial155

New Member
Joined
Jan 23, 2021
Messages
8
Office Version
  1. 365
Maybe I've backed myself into a corner? I track social media metrics. One metric is simple, total followers. Each day we input a new number and that is automated to show up in a table elsewhere. Basically, somebody should be able to input today's data, and then later on the page copy and paste a simple report to send off to the hunchos. =LOOKUP(2,1/(3:3<>""),3:3) is the what I use to return the last non-blank cell, which will always work because we don't input data for days in the future. However, I recently added "Weekly Totals" where it's simply =Sum and with today's data plus the past 6 days. For example AB19 has =sum(ab17:v17) and that brings back the total for the week. However, I can't use the same function I use for totals because in AC, it is still populating with the 0s in the cells that are blank and will return it until 7 days in the future.

1611768255945.png

When trying to pull values for my weekly metrics, how can I get it to stop on Today's date, and not pull from the future dates due to my bad formulas. Do I need to redo how I'm totaling my 7 day totals? Ideally I want a function that could also work for the weekly percentage cells too. My fear is that I backed myself into a corner and need to change how my weekly totals and percentages are calculated to work with a different function that will automate the process.

oh and ignore AB3 4 and 5. We only update that cell at the end of the day, before we send off that report still though
 
Last edited:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,127,398
Messages
5,624,500
Members
416,031
Latest member
Omega67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top