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…
Top comments (0)