Automatically update date within a formula

[DanW3487]

Hi,

I have a sales table which shows how many sales have been made using a formula that Counts data from another report.
Example: =COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,"42838")

42838 being the raw data for the date I want to use. I have a table that shows last Friday - Thursday how many "upgrade paperwork sent" by a certain agent. I am trying to get the report to automatically change the raw date data within the formula to the previous weeks dates.
For example: 2 weeks ago, the date was "42825" (Friday) - "42831" (Thursday) (The table has each day Fri,Mon,Tue,Wed,Thu) and now when I go to the report today to get the data for "42832" - "42838" I have to manually change the raw date for each cell Fri-Thu.
Is there any way I can get this to automatically change if I input what 'todays' raw date is somewhere on the sheet?

P.S I have to use the raw date because the data i gather from can only use this format.

 

[DanW3487]

All of this time I have been trying to put a ridiculous formula in to get the answer. And its just not putting the "" marks that works.

Thank you, you are a genius

 

[wideboydixon]

Haha. Sometimes the answer is just *too* obvious it seems.

WBD

 

[AliGW]

Thanks for the like - glad to have helped!

 

[DanW3487]

Thank you, above the lady answered and it worked great, I have also used your reference in a monthly stats which has worked perfectly. So thanks to you both. How do I say this is now solved?

 

[AliGW]

There is no way to mark a thread as solved on this forum. Just leave it as it is. Again, glad to have helped.