Formula so that a “Not on view” worksheet can dynamically reference the active worksheet

I have an idea with to assist in my other open issue

https://www.excelforum.com/excel-for...te-ranges.html

I want to explore having a “helper” worksheet not in view, that references the current active worksheet, dynamically changing as the worksheet selection changes

I.e. when I’m viewing “Sheet 1”, “helper sheet” looks at the values of the sheet but stays in the background, and these references change when I view “Sheet 2”
This would assist me in keeping the file size and performance manageable in having only 1 helper page (instead of many), so that I could reference and run tests against the “behind the scenes helper page”, calling the results back to be summarised on the sheet im viewing. Each time I view a different sheet the helper page dynamically re-references itself


If anyone can share how to do this, it would be really appreciated.
With thanks

Many thanks SJMaxwell, but I could not get this operating in the way I had in mind

My VBA skills are pretty much non existent,
I can get the active sheet to reference a cell on another (non visible) sheet
But how can you do the other way round - get a non visible worksheet to access and reference a range of cells on the active worksheet
This could populate a "helper worksheet" and the uses and possibilities of this approach are countless.

Hope the attached help put into perspective what I try to describe
Thanks again

This is very clever - ironically I only became aware of the MMULT function about a month ago.
Im not having a lot of luck with this one though - intersecting dates between the "from" column A and "To" column B appear to require an exact match
if for example (and the crux of this thread) is when a date within a "Date" range intersects. eg
From 1/1/2018 (A2) to 10/1/2018 (B2)
needs to be considered for
From 5/1/2018 (A3) to 5/1/2018 (B3)
(as the 5th intersects the entries of row 2)
REALLY curious of how this could be amended thought
Also, the formula would be required on each sheet - ive over 30 sheets, each with three date ranges (1x20 rows, 2x110 rows)

Currently im testing two approaches - the first has every day listed on every worksheet with a "lets see if this falls between any of the entered dates with a simple ">=from" +or "<=to" for each region
This is currently making the file size larger than desired
(ideally I would like a help page to reference the active sheet so that this is only needed to be entered once)

Secondly I have the copy/past macro which drives an active X image to display. This requires the user to manually trigger the checking sequence though

Working through this I'm discovering things that are so applicable to soooo many other tasks though, its been a very beneficial hurdle to have found myself against
Thanks again