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]

For example I named my first 3 sheets respectively 1,2 and 3
B3 returns if it is sequential. Assumed your text is in F3 on every sheet.

hulp tijden (3).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2	Sequential?
3	TRUE				LINE 365 - CENTRAL PARK, 3rd AVENUE, Saturday 07-07-2023
4
5
1

Cell Formulas
Range	Formula
B3	B3	=LET(z,--RIGHT(VSTACK('1:3'!F3),10),MAX(z-VSTACK(TAKE(z,1)-1,DROP(z,-1)))=1)

This formula returns a #name error. What is the z?

 

[JEC]

You probably have to translate the formula. "z" is just a variable

 

[AriannaVV]

You probably have to translate the formula. "z" is just a variable

 

[JEC]

Must be a language issue or you don’t have the new functions

 

[AriannaVV]

Must be a language issue or you don’t have the new functions

You are right. Sorry my mistake. I tested the formula on an older excel version. oops! It works great on 365. Just another one thing. Sometimes I have to check 15 sheets and others I have to check the whole month and that is 30 or 31 sheets depending on month. Is it possible to change the formula to dynamically include new sheets added rather than change it all the time? Thanks!!!

 

[JEC]

Ok, put this formula in the name manager and name it as "all_sheets"

=TOCOL(TEXTAFTER(GET.WORKBOOK(1);"]"))

Then you could use this formula

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

 

[AriannaVV]

Ok, put this formula in the name manager and name it as "all_sheets"

=TOCOL(TEXTAFTER(GET.WORKBOOK(1);"]"))

Then you could use this formula

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

The first formula returns an error message that says "This formula isn't valid"

 

[JEC]

Yes but you have to put that formula directly in the name manager. Otherwise it will not work.
See previous post

 

[AriannaVV]

Yes but you have to put that formula directly in the name manager. Otherwise it will not work.
See previous post

ok. I did so, but now the second formula returns #VALUE error. At first I though that this was because of a sheet named "WORKS" that does not include the header so I changed it's name and copied the header. But the error remains. hmm, that is more complicated than I thought it would be.

 

[JEC]

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