3

This is my sheet: (shared publicly)

https://docs.google.com/spreadsheets/d/1Pz5dkSHwSXne6f_tMoBJ1Fu3Z6JsvjTQgJZhHhpjia0/edit?usp=sharing

In the "Flatten" tab on that sheet, in A1 I have a formula:

={ARRAYFORMULA(split(flatten('Form Responses 1'!A2:A6&"|"&Rates!B1:E1&"|"&Rates!B2:E2&"|"&{'Form Responses 1'!B2:E6}),"|"))}

In that formula, I have to manually change A6 to A7, and E6 to E7 or A6 to A8, and E6 to E8 every time the rows in 'Form Responses 1' changes. As you can see, right now there are 8 rows of data, so I need to manually change 6 to 8. I figured I can get the max number of rows thus:

=MAX(FILTER(ROW('Form Responses 1'!A:A),'Form Responses 1'!A:A<>""))

I put this formula in K1, which shows 8 right now, and I can concatenate the string I want thus: =CONCATENATE("A",K1) to get A8 or =CONCATENATE("E",K1) to get E8.

So, I tried:

={ARRAYFORMULA(split(flatten('Form Responses 1'!A2:CONCATENATE("A",K1)&"|"&Rates!B1:E1&"|"&Rates!B2:E2&"|"&{'Form Responses 1'!B2:CONCATENATE("E",K1)}),"|"))}

But I get the error: 'Argument must be a range'. What would be the correct syntax?

0

2 Answers 2

1

LET Function

The LET function will achieve the same as the named ranges without the need for helper cells, allowing you to combine your logic in a single formula and a single cell.

Adapting Your Formula with LET

The following formula adapts the formula from your answer using LET instead of named ranges and helper cells.

  • dynamicRange1 is now name
  • dynamicRange2 is now qty
=LET(range,'Form Responses 1'!A2:E,
   rows,XMATCH("?*",INDEX(range,,1),2,-1),
   name,ARRAY_CONSTRAIN(INDEX(range,,1),rows,1),
   qty,OFFSET(range,0,1,rows,COLUMNS(range)-1),
   ARRAYFORMULA(SPLIT(FLATTEN(
     name& "|"& Rates!B1:E1& "|"& 
     Rates!B2:E2& "|"& qty), "|")))

Including the Total

The number columns are formatted to hide zero values. You can adapt as you wish. You can also FILTER out the rows without quantities.

=LET(range,'Form Responses 1'!A2:E,
   rows,XMATCH("?*",INDEX(range,,1),2,-1),
   name,ARRAY_CONSTRAIN(INDEX(range,,1),rows,1),
   qty,OFFSET(range,0,1,rows,COLUMNS(range)-1),
   ARRAYFORMULA(SPLIT(FLATTEN(
     name& "|"& Rates!B1:E1& "|"& qty*1& 
     "|"& Rates!B2:E2& "|"& qty*Rates!B2:E2), "|")))

Formula that includes totals

LAMBDA Formula

My own approach is a variation using a LAMBDA function.

=LET(range,'Form Responses 1'!A2:E,
     hdr,OFFSET(range,-1,0,1), rate,Rates!A2:E2,
     WRAPROWS(TOROW(BYROW(range, LAMBDA(r, ARRAYFORMULA(
       TOROW(IF(1/(1/r), { IFERROR(1/hdr, INDEX(r,,1));
         hdr; r; rate; rate*r}),,1)))),3),5))

Single Formula

2
1

Dynamic Named Ranges to the rescue!

I got the idea from Ben Collins' page How to create dynamic named ranges in Google Sheets

Solution:

  1. In K1 , calculate the last populated row in 'Form Responses 1'!A:A using the following formula:
    =MAX(
      FILTER(ROW('Form Responses 1'!A:A),
             'Form Responses 1'!A:A<>""))   
    
  2. In K4 place the formula for the text string that will be used by the first dynamic range:
    ="'Form Responses 1'!A2:A"&K1
    
  3. In K5 place the formula for the text string that will be used by the second dynamic range:
    ="'Form Responses 1'!B2:E"&K1
    
  4. Create two named ranges: *
    1. Range: K4 , Name: dynamicRange1
    2. Range: K5 , Name: dynamicRange2
  5. Use the named ranges in the formula:
    =ARRAYFORMULA(split(
       flatten(INDIRECT(dynamicRange1)&
         "|"& Rates!B1:E1& "|"& Rates!B2:E2& "|"& 
         INDIRECT(dynamicRange2)),
       "|"))
    

 


* Create Named Ranges

  1. Select the range to be named using your mouse or keyboard
  2. Click on the Name box to the left of the Formula bar or jump to it using Ctrl+J
  3. Enter name for your range then Enter or click anywhere else on the sheet.
  4. You can now reference the range in formulas using its name. Steps to name a range

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.