Formula. How to get a range of sheets with a condition in Excel?

Question

excel

How to get sheet range with following condition for formula in Excel? :
sheets whose name contains a certain substring equal to the value from some cell.

PS That is, the user enters a value in a predefined cell label to search for sheets and automatically the formula is applied only to certain sheets. For the photo above, for example, it will be the word "Component", i.e. it will be all sheets containing the substring - Component
Please show an example of such a formula.

Answer 1

Let's say we have two sheets:
Sheet1
Sheet2
On sheet Sheet1 in A1 we write "Sheet2" without quotes.
On sheet Sheet2 in A1 write "FFF" without quotes
On sheet Sheet1 in A2 write the formula
=INDIRECT(A1&"!A1")
We get the value FFF in the cell.
It? But the question is not entirely clear...