Hi Chandoo,
I have a file in which there are two sheets "Sheet1"& "Sheet2". In Sheet1 we paste a raw data from an external file (another excel file). In Sheet2 there is a formulae defined which will count the number of a specific item as per date (e.g. 1st Jan has 20 black leather jackets, 2nd Jan has 50 black leather jackets and so on) from Sheet1. Now the challenge is:
As i mentioned earlier, in Sheet1 we are putting the data from another excel file where the data in only for one month, Now if i put next month's data in Sheet1 (which i am getting from another external excel file) the changes are not reflecting in Sheet2 as in the formulae of COUNTIFS, the criteria is given for a particular date of previous month. In order to make the changes in need to edit the date in the formulae for all cells in Sheet2 The formulae is:
=COUNTIFS(Dump!$C$1:$C$5000,"Black",Dump!$F$1:$F$5000,"Leather Jacket",Dump!$Q$1:$Q$5000,"01/01/2016")
Here every criteria range and criteria is taking from a Sheet called Dump (Sheet1 renamed as 'Dump') and as you can see that the date is 1st Jan 2016. Now the moment date is changing to 01/02/2016 in Sheet1 the change is not reflecting in the current Sheet (Sheet2). Is there any way that i can make the date part variable in countifs formulae, like the moment i put the new months data in Sheet1 the date will change in the countifs formulae in the next sheet.
Note - the date format is DD/MM/YYYY
Hope i am able to explain the problem that i am facing.
Thanks and regards
Anirban
I have a file in which there are two sheets "Sheet1"& "Sheet2". In Sheet1 we paste a raw data from an external file (another excel file). In Sheet2 there is a formulae defined which will count the number of a specific item as per date (e.g. 1st Jan has 20 black leather jackets, 2nd Jan has 50 black leather jackets and so on) from Sheet1. Now the challenge is:
As i mentioned earlier, in Sheet1 we are putting the data from another excel file where the data in only for one month, Now if i put next month's data in Sheet1 (which i am getting from another external excel file) the changes are not reflecting in Sheet2 as in the formulae of COUNTIFS, the criteria is given for a particular date of previous month. In order to make the changes in need to edit the date in the formulae for all cells in Sheet2 The formulae is:
=COUNTIFS(Dump!$C$1:$C$5000,"Black",Dump!$F$1:$F$5000,"Leather Jacket",Dump!$Q$1:$Q$5000,"01/01/2016")
Here every criteria range and criteria is taking from a Sheet called Dump (Sheet1 renamed as 'Dump') and as you can see that the date is 1st Jan 2016. Now the moment date is changing to 01/02/2016 in Sheet1 the change is not reflecting in the current Sheet (Sheet2). Is there any way that i can make the date part variable in countifs formulae, like the moment i put the new months data in Sheet1 the date will change in the countifs formulae in the next sheet.
Note - the date format is DD/MM/YYYY
Hope i am able to explain the problem that i am facing.
Thanks and regards
Anirban