Combining Filter function with Indirect/Concatenate to look up from another worksheet

[wilsonb78]

Hi,
I've created a formula that works fine if it is within the sheet where the data originates, if is filtering the routes our vans will be taking and excludes any blank routes (if a van is not scheduled out that day). But I want to have the summary in another sheet dynamically changing based on today's date.
The filter I have in the sheet named 'Routes' is =FILTER(CE113:CE122,(CE113:CE122<>""),"")
I can get it working in another sheet if I ditch the "<>0" part, but then I end up with an ugly result that has blanks.
The filter I used from the other sheet is this:
=FILTER(INDIRECT(CONCATENATE("Routes!"&A22&113&":"&A22&122),TRUE),(CONCATENATE("Routes!"&A22&113&":"&A22&122)<>""))
Where A22 contains the letters of the column I need to reference and the data is between row 113 and row 122.
I just don't know how/where to get the rule to ignore blanks into the indirect/concatenated formula.

Any help gratefully received.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(d,INDIRECT("Routes!"&A22&"113:"&A22&"122"),FILTER(d,d<>""))

 

[wilsonb78]

Amazing!, Thankyou so much!

 

[Fluff]

You're welcome & thanks for the feedback.