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.

 

[wideboydixon]

It might help if you post some sample data from your table. You could use something like this perhaps:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G
1	Friday:	14/04/2017
2
3	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday
4	42839	42840	42841	42842	42843	42844	42845
Sheet1

Cell Formulas
Range	Formula
A4	=TEXT($B$1+COLUMN()-1,"0")

WBD

 

[DanW3487]

Sorry, im new to this, how do i post the table with cells?

 

[wideboydixon]

See my signature. Look here:

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

WBD

 

[DanW3487]

Apologies I still do not understand, what you have given me is helpful.

But I am trying to automatically update a "number" within a formula (example 1 in original post) How do I input within the formula or sheet to change a certain part of a formula.
Example: =COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,"42838") - I need to automatically update the number in red with the new dates?

 

[AliGW]

You can use the =TODAY() function in any cell in your workbook - it will always return today's date (i.e. will update each time that date changes). So if in your formula you want a date that was a seek ago, replace your hard-coded date with this: TODAY()-7

 

[DanW3487]

Thanks, but the date within the formula is a criteria match for another spreadsheet. So how do I get that "42832" to automatically change using some sort of formula/function? I understand how to use the =today function, I just dont know how to change the criteria "" number that is gathering data from another spreadsheet?

I tried to use cell ref within the "" marks ("A47") but that didnt work, as its looking for that specific number/letter.

 

[AliGW]

You can reference any cell with a date in it with a simple cell reference: A1

If it's on another sheet, then: 'Sheet Two'!A1

If that doesn't answer your query, then you are going to need to give us some sample data so we can see the layout and what exactly you are trying to reference.

 

[wideboydixon]

Ok. I'm assuming the formula parameter *must* be text and not a number. If you're reporting Friday to Thursday then first we need to work out when was last Friday. We can do this using WEEKDAY. Put the following in a cell in Excel and it will go back to the previous Friday:

=TODAY()-WEEKDAY(TODAY(),15)+1

So in your formula you could use the following to get the results for last Friday:

=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+1,"0"))

You can then get details for other previous days by progressively adding one to the formula for Sat/Sun/Mon/Tue/Wed/Thu:

=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+2,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+3,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+4,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+5,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+6,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+7,"0"))

WBD

 

[wideboydixon]

If you want to reference the date that's in A47 then:

=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(A47,"0"))

WBD