Here's a possible solution. It only uses the numbers in column A.

=ARRAYFORMULA(LET(
   n,TOCOL(A2:A,1)
   MAP({"A"&n,"C"&n,"B"&n+1,"B"&n+2,"B"&n+3,"B"&n+4, 
        "D"&n+1,"D"&n+2,"D"&n+3,"D"&n+4,"B"&n+9,"B"&n+10,
        "B"&n+11,"B"&n+12,"D"&n+9,"D"&n+10,"D"&n+11,"D"&n+12},
       LAMBDA(c,LET(x,INDIRECT("'NEW BUNDLES CONTROL IN PROGRESS'!"&c),
         IF(x="",,IF(LEFT(c)="A",REGEXEXTRACT(x,"\d+"),x)))))))

You can use this formula that doesn't use column A. The formula automatically generates the correct sequence of numbers based on where "BUNDLE NO. #" appears on…