If blank between a range put output "Check here" [Picture included]

Here's an picture illustration. Any help is appreciated!Untitled123a.png

There's like 100 of rows with different range. sometimes it's tue - sat, sometimes mon - fri. sometimes wed - sat.

So the formula has to be dynamic such that it works when i fill down. Is this possible?

Week Blank Inbetween Check.xlsx

Why are there only TWO 'Check Heres'? Should there be more? For example, row 591? How does Excel knoiw which should be the start and end days???

that's just a sample. i want a formula that actually outputs "Check here" for me for that column instead .

Start and End days are simple.

The first value that isn't empty = Start day.
The last value that isn't empty = End day.

Let's take a look at the last row in the picture as an example.

Start day = Thursday
End day = Sat

Although, naturally there isn't an issue for this row since there is no blank cells inbetween start and end days. So we don't need "Check here" for that row.

Row 591:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	Q	R	S	T	U	V	W
591	Mon	Tue		Thu	Fri	Sat

Sheet: Sheet1

Wednesday is missing. Why should it not be flagged? There are other rows like this. I am not confident of the integrity of your sample workbook, which needs to be 100% accurate.

It does NOT help to provide sample data that does not show all the expected results! Please don't do this again.

In Y583:

=BYROW(Q583:W613,LAMBDA(x,LET(r,IFNA(XMATCH(Q$2:W$2,x),"|"),l,MIN(r),h,MAX(r),s,SEQUENCE(,h,l),f,FILTER(r,r<>"|"),tf,IFERROR(s=f,""),IF(SUMPRODUCT((tf=FALSE)*(tf<>""))>0,"Check Here",""))))

Change the range in RED to suit.

Yes, of course - that is what your forum profile says. If you need this to work in an older version , then you need to change your forum profile to the OLDEST version that any solution must work with.

Please don't blame me - NOWHERE did you say that it had to work with an older version, so why would I not use 365 functions?

I have worked quite hard on this for you, so if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Thanks - please make sure that you don't waste our time in future like this.

You can't have a SPILL (dynamic) formula with 2019. I might have another look later, but I think someone else will have to help, as I don't have Excel 2019 for testing.

No, they really aren't!

In Excel, go to File > Account and it will tell you right there which version you have. Please check again and make sure that your profile is accurate.

Also, bookmark this page: https://bettersolutions.com/excel/fu...-functions.htm

This SHOULD work in 2021, but you have to drag copy it down:

=LET(r,IFNA(XMATCH(Q$2:W$2,Q583:W583),"|"),l,MIN(r),h,MAX(r),s,SEQUENCE(,h,l),f,FILTER(r,r<>"|"),tf,IFERROR(s=f,""),IF(SUMPRODUCT((tf=FALSE)*(tf<>""))>0,"Check Here",""))

See post #13.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

You're welcome. I am glad it was not for nothing!