Short answer
Use something like ROW(A1:A21)
Explantation
ROW(cell_reference) returns the row number of the specified reference. When the reference is a range and the function is inside of a ARRAYFORMULA it will return a an array of a sequence of numbers starting on the row number corresponding to the start cell reference and ending on the row number corresponding to the end cell reference.
There are several Q&A on this site that use this.
Answer from Rubén - Volunteer Moderator - on Stack ExchangeWhat Is a Data Range in Google Sheets?
A data range is a set of cells that you want to use in a function or formula. It’s another name for range. The two names are interchangeable.
How Do You Calculate Total in Google Sheets?
Formulas in Google Sheets can automatically calculate the total of a certain range of cells. If the values inside the cells are changed, the total will then adjust accordingly. The usual function used is SUM which is the total of all the values in the argument. The syntax of this function is =SUM(x:y) where x and y is the start and end of your range accordingly. For example, the total of a range from A1 to C3 will be written as =SUM(A1:C3).
What Is a Valid Range in Google Sheets?
Depending on the formula you use, some values will not be accepted as an argument. For example, the cell value TRUE can’t be used in the formula =SUM() as it’s not a calculable numeric value. A valid range is a set of cells containing data that a formula will accept as an argument. If there is a cell that has an unaccepted input, then the range isn’t valid. Invalid ranges can also occur when either the first or last point of the range has a value that results in an error.
Short answer
Use something like ROW(A1:A21)
Explantation
ROW(cell_reference) returns the row number of the specified reference. When the reference is a range and the function is inside of a ARRAYFORMULA it will return a an array of a sequence of numbers starting on the row number corresponding to the start cell reference and ending on the row number corresponding to the end cell reference.
There are several Q&A on this site that use this.
=ARRAYFORMULA( VLOOKUP(A2:A, Sheet2!A:Z, SEQUENCE(1,25,2,1), false))
SEQUENCE(1, [length], [start], [step]) returns a list (in this case a row) of numbers of length [length] starting with [start] incremented by [step].
Generally, if your lookup column is in the first position of your lookup table, [start] will be 2. Make sure the length you specify is the same as the number of columns in the lookup table, else you'll get an out of range error.
Hi there,
I'm making a spreadsheet and I'm trying to find a formula that creates a number range. For example, I want the formula to pretty much say '100-105' or '200-250' rather than spitting out a single number. I've also attached an image below of what I'm looking for.
Thanks for your help :)