Dynamically addressing different sheets ?

[catalincirjan]

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

 

[CA_Punit]

Instead of Concat try to use

INDIRECT("'"&J1&"'!F3:F500")

 

[catalincirjan]

It worked, but without the " ' ". Probably due to different version. Thank you very much!