Dynamically addressing different sheets ?

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. 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
 

Attachments

  • image001.png
    image001.png
    72.5 KB · Views: 6

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

catalincirjan

New Member
Joined
Aug 28, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
It worked, but without the " ' ". Probably due to different version. Thank you very much!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,165
Messages
5,623,115
Members
415,955
Latest member
ssheldon2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top