# Use variable in row reference in formula

This is my sheet: (shared publicly)

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?

# 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.

• `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), "|")))
``````

# 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))
``````

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.