# Create variable function arguments in Google Sheets

I know that my question illustrates a fundamental lack of understanding, but I have hacked together a sheet that almost works except...

I am importing data from googlefinance then operating on the data. The problem is that the length of the data is not always the same. So my problem boils down to this:

Assume column A and B contains data e.g. A1-A10 contains date and B1-B10 contains price, cell c1 contains a COUNT of the data in column A so I know how many data points.

`=TREND(B1:B10,A1:A10)` will result in a trend line if I get all 10 data points and fail if not

How can I modify the TREND function arguments so that "A10" is instead "A(0 + the contents of C1)"?

Use a combination of `ADDRESS()` and `INDIRECT`

• Cell C1 = count of the number of rows. This can be used to generate the second half of a range.
• Let's assume that the value in C1 = 9.

• `address(\$C\$1,1,4,true)` = A9
• `address(\$C\$1,2,4,true)` = B9
• add a prefix of "A1:" and "B1:" to generate the string value of the ranges
• `"A1:"&address(\$C\$1,1,4,true)` = A1:A9
• `"B1:"&address(\$C\$1,2,4,true)` = B1:B9

INDIRECT

• `INDIRECT` returns a cell reference specified by a string.
• `indirect("B1:"&address(\$C\$1,2,4,true))` - returns values in the range B1:B9
• `indirect("A1:"&address(\$C\$1,1,4,true))` - returns values in the range A1:A9

TREND

`=TREND(indirect("B1:"&address(\$C\$1,2,4,true)),indirect("A1:"&address(\$C\$1,1,4,true)))`

• Really appreciate the clarity of your solution - thank you Commented Mar 26, 2022 at 11:35
• Glad it works for you. You might consider “accepting” the answer. Commented Mar 26, 2022 at 22:11