Search code examples
google-sheetsgoogle-sheets-formula

Google sheet Formula cell referencing

I have a problem regarding cell reference. I wanted to get the data from the other tab which I already got the row number and the tab name for each.

The tab name will then be cleaned up using MID and FIND to output the correct Bundle number

Expected output will be 2,3 and 4.

This is coming from below.

Also, I will attached the file here: https://docs.google.com/spreadsheets/d/1ikAH6-NKHBL9piJ4nJY_hyLdTjtPbi2VK7Ct1kIFelg/edit?gid=1255002068#gid=1255002068

If you can help me with this that would be great.

Solution

  • 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 the first sheet

    =ARRAYFORMULA(LET(
   r,'NEW BUNDLES CONTROL IN PROGRESS'!A:A,
   n,FILTER(ROW(r),REGEXMATCH(r,"BUNDLE NO\. \d+")),
   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)))))))