How to stack columns of differents length in Excel

In have a woorkbook with many sheets. All sheets have the same format of content. I want to stack cells from one specific column of many sheets.

I give in cell A1 the list of the sheets : data;Blad7

I can display the content of the cells A1 :

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  Test; MAP(Sheets; LAMBDA(sheet;
                           INDIRECT("'" & sheet & "'!A1")
                          )
           );
  Test
)

I gives a 1Col x 2Rows result with the cells A1 of each sheet. Ok. Now I can check what I effectively want to stack :

=FILTER(INDIRECT("'" & "data" & "'!D:D"); INDIRECT("'" & "data" & "'!D:D")<>"")
=FILTER(INDIRECT("'" & "Blad7" & "'!D:D"); INDIRECT("'" & "Blad7" & "'!D:D")<>"")

I gives two spilled results of 18 and 52 lignes respectively. Perfect.

But when I try to combine these two test, like this, it doesn't work. I get #CALC! :

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  Test; MAP(Sheets; LAMBDA(sheet;
                           FILTER(INDIRECT("'" & sheet & "'!D:D"); INDIRECT("'" & sheet & "'!D:D")<>"")
                          )
           );
  Test
)

I also tried with BYROW instead of MAP. My final goal should by this :

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  Test; VSTACK(MAP(Sheets; LAMBDA(sheet;
                           FILTER(INDIRECT("'" & sheet & "'!D:D"); INDIRECT("'" & sheet & "'!D:D")<>"")
                          )
           ));
  Test
)

Any idea why it doesn't work ?

The solution here is:

=DROP(REDUCE("";TRANSPOSE(TEXTSPLIT(A1; ";"));
    LAMBDA(a;sheet;
      VSTACK(a;LET(tr;INDIRECT("'" & sheet & "'!D:D");FILTER(tr;tr<>"")))));1)

The problem in your formulas is the MAP function which maps a cell to the cell only, no array support. The only way to join arrays is the REDUCE function in combination with VSTACK or HSTACK.