Unfortunately, using this technique, you can not omit hidden sheets.

You can specify which sheet name you do want by modifying this expression:

ROWS(A$1:A1)

As you drag copy the formula the ROWS function is used as an incrementer to tell the INDEX function which sheet name to return. The sheet names are "stored" in the INDEX function in "positions" starting from position 1 to position N (the total number of sheet names).

Such that:

ROWS(A$1:A1) = sheet1
ROWS(A$1:A2) = sheet2
ROWS(A$1:A3) = sheet3
etc
etc

So, if you want to start the list with sheet2 then change ROWS(A$1:A1) to ROWS(A$1:A2).

We could probably put something in the formula to omit the first and last sheet name by comparing the number of rows the formula is being copied to against the total number of sheets.