Check if date is in sequencial order in all sheets

[AriannaVV]

Hi all.
I have a workbook with 31 sheets. In each sheet I have a title like: Line 364 _ Central Park, Beach, Monday 01.08.2023. The format is dd.mm.yyyy. The date changes sequencial for each sheet. Let's say if the first sheet is ...Monday 01.08.2023 next sheet is Tuesday 02.08.2023. I need to check if the dates in all sheets are in sequencial order and there is no error in them (like different month for example "01.07.2023"). If there is an error the code should stop and a pop up message tell the sheet where the error is.

Can someone help please?

 

[AriannaVV]

How did you name the formula in the name manager? It has to be exactly “all_sheets”

I copied it so to be sure

 

[JEC]

If you want to exclude some sheets you can put an extra filter around the all_sheets array.

Here it is working as intended though

 

[AriannaVV]

If you want to exclude some sheets you can put an extra filter around the all_sheets array.

Here it is working as intended though

 

[AriannaVV]

Seems to work ok in a workbook with specific sheets. As the previous formula did, before the name option. If I add new sheets it can't calculate them and returns #VALUE error. If I delete them it works again. This is not what I need though. Nevermind thanks for your time anyway. Maybe a vba could do the job. Thanks for your effort!

 

[JEC]

Yes if you add new sheets without that header and valid date in F3, it will fail. Could be worked around. But can you post a dropbox link or something?

 

[JEC]

=LET(r;DROP(REDUCE("";all_sheets;LAMBDA(a;b;VSTACK(a;INDIRECT("'"&b&"'!F3"))));1);z;FILTER(--RIGHT(r;10);ISNUMBER(--RIGHT(r;10)));MAX(z-VSTACK(TAKE(z;1)-1;DROP(z;-1)))=1)

 

[AriannaVV]

=LET(r;DROP(REDUCE("";all_sheets;LAMBDA(a;b;VSTACK(a;INDIRECT("'"&b&"'!F3"))));1);z;FILTER(--RIGHT(r;10);ISNUMBER(--RIGHT(r;10)));MAX(z-VSTACK(TAKE(z;1)-1;DROP(z;-1)))=1)

That works perfect and excludes the unwanted sheet. I think we are close to finalize this. Now there is one more issue. When I add sheets, if i accidentaly bring the same date as previous, let's say : sheet 11 exists in the workbook with date value 11/08/2023, if I add sheet 12 with same date value 11/08/2023 does not return a FALSE. It remains TRUE wich is not correct, cause it's wrong to me if two sheets have the same date on header. On the contrary it works fine if the error is in month, that is in case of 11/07 instead of 11/08 it returns a FALSE as it should be. Any ideas why this happens?

 

[JEC]

Yes I know why, but probably tomorrow I can look again

 

[AriannaVV]

Yes I know why, but probably tomorrow I can look again

Thank you so much!!!!

 

[JEC]

Hi, try this

=LET(r;DROP(REDUCE("";all_sheets;LAMBDA(a;b;VSTACK(a;INDIRECT("'"&b&"'!F3"))));1);s;--RIGHT(r;10);z;FILTER(s;ISNUMBER(s));SUM(--(z-VSTACK(TAKE(z;1)-1;DROP(z;-1))<>1))=0)