catalincirjan
New Member
- Joined
- Aug 28, 2020
- Messages
- 18
- Office Version
- 2016
- Platform
- Windows
Hello!
This is an improvement I am trying to make to a formula that counts differences between two arrays.
Now, I am trying to make this more dinamyc.
First of all:
The formula (sample) that I used to count the number of differences is
=COUNTIF(Tracks!F3:U500,"Closed")-COUNTIF(Reference!F3:U500,"Closed")
My initial plan was to update the Reference sheet every week.
Now, I want to make a new sheet every Friday and allow the user to see the "report" between wich sheet is most relevant for him.
So I will have let's say 4 sheet (Present,28Aug, 4Sep, 11Sep) /// I want to change the name of initial sheet Tracks to Present
On the top I made a simple drop down to select the dates (H1:J1)
Now I have tried to change the formula to
=COUNTIF(CONCAT(J1,"!")F3:F500,"Closed")-COUNTIF(CONCAT(I1,"!")F3:F500,"Closed")
Also tried =COUNTIF(CONCAT(J1,"!","F3:F500","Closed") - still not working
This way, when somebody changes the date for start and/or end the person can see differences between other sheets.
The concat/concatenate formula works ok when I choose format as text.
The end formula however doesn't seem to like to reference a sheet this way.
Any ideas on how do go about this?
P.S. Hope I explained well enough
P.S.S. Ignore the INDIRECT part, as I realised it was an unnecessary step
This is an improvement I am trying to make to a formula that counts differences between two arrays.
Now, I am trying to make this more dinamyc.
First of all:
The formula (sample) that I used to count the number of differences is
=COUNTIF(Tracks!F3:U500,"Closed")-COUNTIF(Reference!F3:U500,"Closed")
My initial plan was to update the Reference sheet every week.
Now, I want to make a new sheet every Friday and allow the user to see the "report" between wich sheet is most relevant for him.
So I will have let's say 4 sheet (Present,28Aug, 4Sep, 11Sep) /// I want to change the name of initial sheet Tracks to Present
On the top I made a simple drop down to select the dates (H1:J1)
Now I have tried to change the formula to
=COUNTIF(CONCAT(J1,"!")F3:F500,"Closed")-COUNTIF(CONCAT(I1,"!")F3:F500,"Closed")
Also tried =COUNTIF(CONCAT(J1,"!","F3:F500","Closed") - still not working
This way, when somebody changes the date for start and/or end the person can see differences between other sheets.
The concat/concatenate formula works ok when I choose format as text.
The end formula however doesn't seem to like to reference a sheet this way.
Any ideas on how do go about this?
P.S. Hope I explained well enough
P.S.S. Ignore the INDIRECT part, as I realised it was an unnecessary step