I am making an attempt to automate a reporting course of the place by we summarize information from three separate experiences for the newest Friday. Typically for no matter motive a number of of those three experiences will not run; at this level it turns into extra essential to check the three experiences like for like on a unique day, than to have one lacking on a Friday (assuming there are any for the Friday).

The report information is in three tables: “aaa”, “bbb”, “ccc”. Every by itself worksheet (“AAA”, “BBB”, and “CCC” respectively) and every has a discipline known as “Date”. “O34” is the date of the final Friday on the worksheet “Abstract” the place the abstract information and components reside.

I’ve managed to create an array components which seems to be for the closest out there report date on or earlier than final Friday accross all three experiences as under (additionally individually for every report if it is damaged up). This solely returns the newest report date for any of the three experiences. I am having problem figuring out how one can search for the newest date the place that date is current on all three of the experiences.

i.e. If “aaa” is 8/5/20, “bbb” is 8/5/20, and “ccc” is 5/5/20, I am at present returning 8/5/20 however the final date for one of many experiences is earlier the place as I would like all of them to match.

{=MAX(INDEX(aaa[Date],MATCH(MIN(ABS(aaa[Date]-O34)),ABS(aaa[Date]-O34),0)),INDEX(bbb[Date],MATCH(MIN(ABS(bbb[Date]-O34)),ABS(bbb[Date]-O34),0)),INDEX(ccc[Date],MATCH(MIN(ABS(ccc[Date]-O34)),ABS(ccc[Date]-O34),0)))}

Whether it is simpler or solely potential to unravel this subject in VBA then that may most likely be preferable, but it surely’s extra essential I can take away the necessity for guide intervention than how it’s achieved.

Thanks prematurely to anybody who’s in a position to assist or takes the time to think about it! Apologies if the reply is already on the market however I’ve not discovered one shut sufficient.