Dynamic table calculations

Question

I am attempting to create a dynamic table that will execute formulas based on a selection from a drop-down menu.

I have created a test spreadsheet to support my request, which can be found here ⬅️ ⭐️

In the upper table, I input values, while the bottom table performs calculations.

My goal is to have the calculations apply to the range selected from the upper table whenever I change the value in the drop-down menu in the bottom table (B37:B61).

In the sandbox tab, the bottom table contains formulas for the first item in the list. These formulas should update when I select a different item from the list, and they are based on the corresponding range in the upper table.

Thank you!

Answer 1

Use filter() to get the columns where the item labels in row 2 match the value in the dropdown cell B38, like this:

=let( 
  fillRight_, lambda(d,byrow(array_constrain(d,max(bycol(d,lambda(c,index(ifna(xmatch("?*",to_text(c),2,-1)))))),columns(d)),lambda(r,scan(,r,lambda(a,c,if(c="",a,c)))))), 
  unpivot_, lambda(data, numFixedCols, numColsPerGroup, let(k,n(numFixedCols),d,if(k,data,hstack(sequence(rows(data)),data)),f,if(k,k,1),g,numColsPerGroup,r_,lambda(t,o,h,chooserows(t,sequence(h,1,o))),c_,lambda(t,o,w,choosecols(t,sequence(1,w,o))),s_,lambda(r,c,h,w,r_(c_(d,c,w),r,h)),h,hstack(s_(1,1,1,f),"PSP","Total"),i,sequence(1,(columns(d)-f)/g,f+1,g),a,reduce(h,sequence(rows(d)-1,1,2),lambda(a,r,let(x,s_(r,1,1,f),b,reduce(tocol(æ,2),i,lambda(y,c,let(z,s_(r,c,1,g),if(single(z)="",y,vstack(y,hstack(x,s_(1,c,1,1),z)))))),vstack(a,b)))),if(k,a,choosecols(a,sequence(1,columns(a)-1,2))))), 

  selectedItem, B38, 
  percentages, hstack("2.5%", "97.5%", "12%", "88%", "35%", "65%"), 
  itemLabels, hstack(selectedItem, fillRight_(C2:V2)), 
  dates, vstack("Week", arrayformula(year(B4:B33) & " week " & weeknum(B4:B33, 2))), 
  data, filter(hstack(dates, C3:V33), isblank(selectedItem) + (selectedItem = itemLabels)), 
  table, unpivot_(data, 1, 1), 

  totals, query(table, "select 'Total', Col2, sum(Col3) group by Col2 label 'Total' '" & if(isblank(selectedItem), "Grand", selectedItem) & " Total' ", 1), 
  weekly, query(table, "select Col1, Col2, sum(Col3) group by Col1, Col2 label Col1 '', Col2 '', sum(Col3) '' ", 1), 
  all, vstack(totals, weekly), 
  numbers, choosecols(all, 3), 
  cuts, arrayformula(iferror(numbers * percentages, percentages)), 

  hstack(all, cuts) 
)

To show the results for an item, choose the item in cell B38. To show the grand totals, clear cell B38.

See filter().

Answer 2

I resolved my problem using the following formula:

=LET(
   item,INDEX($C$2:$2,,MATCH($B$37,$C$2:$2,0)),
   pcts,{1,0.025,0.975,0.12,0.88,0.35,0.65},
   headers,OFFSET(item,1,0,1,5),
   data,OFFSET(item,2,0,32,5),
     MAKEARRAY(25,8,LAMBDA(r,c,
       IFS(c=1,INDEX(headers,MOD(r-1,5)+1),
         r<=5,SUM(INDEX(data,,r))*INDEX(pcts,,c-1),
         TRUE,SUM(CHOOSEROWS(INDEX(data,,MOD(r-1,5)+1),
           SEQUENCE(8,1,INT((r-6)/5)*8+1)))
       *INDEX(pcts,,c-1)))))