How can I make a dynamic range based on cell condition in excel 365

Question

on sheet 1 A1 I have the following formula

=IF(INDIRECT("'"&K5&"'!$a$9:$e$24")="","",INDIRECT("'"&K5&"'!$a$9:$e$24"))

This pulls a range from another sheet and keeps cells blank if they are blank. On the other sheets I have the date listed in column A I need the range itself (a9:e24) to be variable based on the first date found after 1/1/2024 and then add 15 So if the sheet called upon has the 1st instance of a date after 1/1/2024 in cell A10 I need the range to be (A10:E25)

I am using excel 365 free so can't use VBA and I know nothing about it anyway. I've run into a dead end or i don't know how to ask google how to do this. any help is appreciated

Answer 1

Copy Dynamically From Another Sheet

=LET(sheet,K5,data,"A9:E1000",date_col,1,after_date,"2024/1/1",rows_count,16,
     a,INDIRECT("'"&sheet&"'!"&data),
     b,IF(a="","",a),
     m,XMATCH(TRUE,CHOOSECOLS(b,date_col)>DATEVALUE(after_date)),
IF(ISNA(m),"",CHOOSEROWS(b,SEQUENCE(rows_count,,m))))

• Make sure 1000 is big enough to avoid a #VALUE error.
• The screenshot illustrates that the source data needs to be sorted by date i.e. XMATCH finds the first (top-most) 'after' date.

Answer 2

MATCH function find the first occurence of a value.

=IF(INDIRECT("'"&K5&"'!"&"A"&MATCH("1/1/2024",INDIRECT(K5&"!A:A"),0)&":E"&MATCH("1/1/2024",INDIRECT(K5&"!A:A"),0)+15)="","",INDIRECT("'"&K5&"'!"&"A"&MATCH("1/1/2024",INDIRECT(K5&"!A:A"),0)&":E"&MATCH("1/1/2024",INDIRECT(K5&"!A:A"),0)+15))

ADDED:

The reason that MATCH is looking for the specific type as in the lookup value. Let define a cell e.g. K6 for the date you are looking for. With this you can dynamically define the date.

=IF(INDIRECT("'"&K5&"'!"&"A"&MATCH(K6,INDIRECT(K5&"!A:A"),0)&":E"&MATCH(K6,INDIRECT(K5&"!A:A"),0)+15)="","",INDIRECT("'"&K5&"'!"&"A"&MATCH(K6,INDIRECT(K5&"!A:A"),0)&":E"&MATCH(K6,INDIRECT(K5&"!A:A"),0)+15))

This is the result: