In column A I have numbers that are arranged in ascending order, in a range that goes from A! up to A174. If I have to select a certain number of cells, for example, 30 cells, and my initial cell is in line A169, I have to do it manually, starting with cell A169 and continuing the selection from cell A1 until completing the 30 cells.


Is there a way to do this operation using formulas?

Thanks in advance

Are the numbers random or sequential? What is the criteria for selection? Once selected, how should they be identified? Moved? Help us to help you with specific information. Crystal balls are not available any longer to help us.

I'm really confused here! Do you want to select cell A169 and cells A1:A29, or what?

I need to make a selection that starts in cell A169 and ends in cell A29. The matrix has 173 rows - it starts at A1 and ends at A173.
I tried =INDEX($A$1:$A$173, ROW(INDEX($A$1:$A$173, 169)):ROW(INDEX($A$1:$A$173, 29))) but it gave an error.

At start you declared you need to select 30 cells, now it is 140 cells!?

OK! Let's it be 140 (or less, as you can't select 140 cells with your conditions, when you are e.g. on row 10).

Define a dynamic range (Main Menu>Formulas>Name Manager) e.g. dnRange140 as
Now you can use this dynamic name a reference, which returns a range of cells depending on your active row number, and ending at your active row. P.e. when you enter into D1 the formula =COUNT(dnRange140), and copy it down to D173, the returned values start from 1 in first row to 140 in 140 row, and remains 140 after that. In case you activate a cell in row 169, the number of cells in dnRange140 is 140, and the returned range is A29:A169.

Edit. When you activate any cell on this sheet, then open Name Manager from menu, activate this Named Range, and then click on Refers To field, the current range of it will be marked as active on your sheet - so you can see which cells are included at this moment.