Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Automatically Increase Number of Row for Ceratin Range Address when Copying formula

Automatically increase the number of rows for a certain range when copying a formula

Probably you have ever wondered how automatically when copying a formula to increase the number of rows for a specific value. Sometimes we need extra column with absolute or relative addresses a range of data that is used as a source address or range of cells from which you need to get a result or copy a specific value.

In this example, I'll show you how we can create a formula that will automatically change the range of addresses when copying formulas. Also with the results of the formula I will show where the respective ranges of address, we can use.
Starting situation, see the picture below.

You note in the column 'C' cell ranges, which I highlighted in different colors (for ease of distinction for making this tutorial).
Our goal is for each different range of cells to return data from the first cell and the last cell in a certain range. In the column 'E' you note already finished results after I created a formula that I will explain in this tutorial. Columns 'F' and 'G' it is necessary to display data from the first and last cell in the respective range.


Immediately you might notice that the column 'E' auxiliary, which we use for further calculations. In this column, there are ranges of cells that are formatted as text, and we want when copying modified cell ranges.

Automatically increase the number of rows in an absolute address range when copying a formula

How to create a formula that will automatically increase the number of rows and change the range of cells when copying.

So, we need to create a formula to increase the range according to certain criteria. In this case, I want when copying increase ie. Change the range of cells for a group of rows where the condition is nine rows (9 rows). After copying the formula down, I want to have ranges that are automatically shifted for nine rows.

In the pictures below I will show step by step how we can create a formula of more nested functions. In fact, the goal of this tutorial is to show how to do step by step when you want to nest more functions in a single formula.

Automatically increase the range of data for a number of rows when copying the formula.
In the picture above you notice, six steps that I had imagined it would be necessary when creating more complex formula. Immediately you notice the results of which I will explain in the next steps of this tutorial. The numbers rows returned by the formula below are based on the first row in the cell 'C2' where begins counting 1,2,3,4 ...

The first part of the address range in the cell 'E2' (separator is a colon)


1st (first step)

As we said, in a cell 'C2' is a fixed address range (as text) and it is default address range. We will first set up a formula in cell 'E3'.

2nd (second step)

The cell 'F12' is the following formula is below.
(This formula returns as a result of our first two digits of the text in a cell 'E2', these two digits are fixed and do not change. This formula will serve us later that the result merge (concatenate) with the other results)
=LEFT(E2;2)

3rd (third step)

The cell 'G12' is the following formula below.
(This formula contains the previous, which is nested, this formula below returns the first part of the range of cells 'E2'. So separator is a colon (:). Everything is in front of the colon in the text in a cell 'E2' this formula will be displayed. This is important because when you copy the formula down, there will be a number of rows that will have more digits that are not fixed length of the number of digits.
As you can see in the cell 'G12' is the result of the text '$C2'. When this formula is copied down, in the ninth row, the formula to return the result '$C65. I hope you understand what I wanted to say)
=IFERROR(LEFT(E2;FIND(":";E2)-1);E2)

4th (fourth step)

The cell 'I12' is the following formula below.
(This formula uses the result of cells 'G12' and returns us all the numbers that follow the first two characters '$ C'. Of course here will be more digit numbers after multiple copy the formula down. Note the number '7' in the formula I put him optionally, that number may be lower or higher and it sets the maximum number of digits rows when copying. It all depends on how many rows you expect after copying).
=MID(G12;3;7)

5th (fifth step)

The cell 'J12' is the following formula is below.
(This formula is linked with the cell 'I2' and as a result returns the number 10. This is the number of rows for following range that we want to create. Each range contains nine rows, so we need in the next tenth row to have a new range. Therefore, the formula in cell 'I2' returned to us is the result of the second. To this number add eight rows to a total of ten rows).
=MID(G12;3;7)+8

6th (sixth step)

The cell 'K12' is the following formula below. (This formula returns the final result for first part of the address range in relation to the source data in the 'E2' cell. This formula consists of two formulas (Concatenate) as follows below.
=LEFT(E2;2)&MID(G12;3;7)+9

Take a look at the formula 'G12', this formula we will put in place the cell address 'G12' in the formula above, and our formula looks like this.
=LEFT(E2;2)&MID(IFERROR(LEFT(E2;FIND(":";E2)-1);E2);3;7)+9  return result '$C11'

The second part of the address range in the cell 'E2' (Excel separator range is colon)


In cells that are located in the thirteenth row, the formula returns the necessary results for second part of the range from cell 'E2'. The principle of the calculation is the same as previously described in the section above, this tutorial.

1st (first step)

The cell 'F13' is the following formula is below. (The formula is the same as the cell 'F12' in the previous part tutorials)
=LEFT(E2;2)

2nd (second step)

The cell 'G13' is the following formula below.
(This formula is the same as in the cell 'K12'. This formula we need as the first part of the final results)
=LEFT(E2;2)&MID(IFERROR(LEFT(E2;FIND(":";E2)-1);E2);3;7)+9

3rd (third step)

The cell 'H13' is the following formula is below.
(This formula is, in fact, a fixed part of the second part of the address range. I am here merged (Concatenate) colon and the first two characters from the cells 'E2'. Of course, we can do this, because it's just one column. This formula will later merge with other formulas to get the final formula).
=":"&LEFT(E2;2)

4th (fourth step)

The cell 'I13' is the following formula is below.
(This formula finds the position of the colon (:) in the text of the cells 'E2'. In this case, it is the fourth digit, to which we add the number 3 and as a result, we have a number 7, which will be our starting position in the following formula).
=FIND(":";E2;1)+3

5th (fourth step)

The cell 'J13' is the following formula is below.
(This formula returns as a result of all of the digits after '$8:$C'text in a cell 'E2'). In our case, it is number 10 out of the cell 'E2'. Attention, the number 10 has nothing to do with the cell 'J12' above. The formula will return a maximum of seven digits. If you look at the seventh position in the text of the cells 'E2' is the number 1 and the eighth position of the digit 0).
=MID(E2;FIND(":";E2;1)+3;7)

6th (sixth step)

In the cell 'K13' is the following formula below. (This formula returns the final result for the second part of the address range in relation to the source data in the 'E2' cell. This formula consists of two formulas as follows below).
=":"&LEFT(E2;2)&MID(E2;FIND(":";E2;1)+3;7)+9

The final formula in cell 'E3', which we can copy down is the next. When copying this formula you note that automatically increase rows for a particular space when copying the formula down.
=LEFT(E2;2)&MID(IFERROR(LEFT(E2;FIND(":";E2)-1);E2);3;7)+9&":"&LEFT(E2;2)&MID(E2;FIND(":";E2;1)+3;7)+9

At the end remains to put formula in cells 'F2' and 'G2'.

Displaying the first and last values in the column different from zero

The formula in cell 'F2' is as follows below (you copy the formula down). (The formula in cell 'F2', used the range of data in the formula and return the result. This formula, we cannot copy down it will return an incorrect result. This is because it ranges from cells changed when copying down if you set the absolute address again would not be good.)
=INDEX(C2:C10;MATCH(1;INDEX(ISNUMBER(C2:C10)*(C2:C10>0);0);0))

This formula below is attached for the cell 'E2' and we copy it down. This formula is, in fact, the same as the previous one but uses the INDIRECT function that is related for cell E2. The formula will return the first value that is greater than or different from zero (0), of course, from the respective range.
=INDEX(INDIRECT(E2);MATCH(1;INDEX(ISNUMBER(INDIRECT(E2))*(INDIRECT(E2)>0);0);0))

The formula in cell 'G2' is as follows below (copy the formula down). (This formula as a result of returns last filled cell that is different or greater than zero (0) from the specified range in the cell 'E2', for these two formulas is worth the same as for the previous two)
=LOOKUP(2;1/(C2:C10<>0);C2:C10)
=LOOKUP(2;1/(INDIRECT(E2)<>0);INDIRECT(E2))


You notice that I used here the INDIRECT function that in relation to the address range from the cell 'E2' returns the correct result. I hope you understand how to create a formula that will increase when copying a range of rows for a certain number of rows.

Automatically increasing the range of 10 orders (block cells increase range by 10 rows)

The example below shows how when copying, you can increase the number of orders for ten (10).

The task is as follows:
We need to find MAX and MIN value in a certain block range of cells. We need to create a formula that will when copying down, increase the range of 10 rows.

In the picture below you see the following cell ranges. The shift block cell is ten rows.
- $A2:$A11, B2:B11, C2;C11
- $A12:$A21, B12:B21, C11:C21
- $A22:$A31, B22:C31, C22:C31

So, when copying a formula we need to automatically increase the range of rows from the 'B2: B11' => 'B12:B21' => 'B22:B31'

Automatically increase the range of 10 of rows when copying a formula


In the picture above you note the results in the column 'E', 'F', 'G', 'H'.

The formula in cell 'E2' is the next. (Formula returns the maximum value (the highest number) from a range of cells 'B2:B11'.
When you copy the formula down, it automatically increases the number of row for the ten of rows (block cell range)
=IF(MAX(INDIRECT("$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9))=0;"";MAX(INDIRECT("$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9)))

The formula in cell 'F2' in the following. (Formula returns the date in column 'A' for the maximum value from range 'B2:B11'.
=IFERROR(INDEX(INDIRECT("$"&CHAR(64+COLUMN(A1))&10*ROW()-18&":$"&CHAR(64+COLUMN(A1))&""&10*ROW()-9);MATCH(E2;INDIRECT("$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9);0));"")
The formula in cell 'G2' is the following. (Formula returns the minimum value (lowest number) from the range 'C2:C11'.
=IF(MIN(INDIRECT("$"&CHAR(64+COLUMN(C1))&10*ROW()-18&":$"&CHAR(64+COLUMN(C1))&""&10*ROW()-9))=0;"";MIN(INDIRECT("$"&CHAR(64+COLUMN(C1))&10*ROW()-18&":$"&CHAR(64+COLUMN(C1))&""&10*ROW()-9)))

The formula in cell 'H2' is the following. (Formula returns the date in column 'A' for the maximum value from range 'C2:C11'.
=IFERROR(INDEX(INDIRECT("$"&CHAR(64+COLUMN(A1))&10*ROW()-18&":$"&CHAR(64+COLUMN(A1))&""&10*ROW()-9);MATCH(G2;INDIRECT("$"&CHAR(64+COLUMN(C1))&10*ROW()-18&":$"&CHAR(64+COLUMN(C1))&""&10*ROW()-9);0));"")

The formula in cell 'J2' is just an example of how to change a range of cells when copying down, but it looks like below.
="$"&CHAR(64+COLUMN(B1))&10*ROW()-18&":$"&CHAR(64+COLUMN(B1))&""&10*ROW()-9

Here I want to mention that this formula that you see above depends in which row is placed. If this formula is copied to the cell 'J14', ie. Start creating the ranges in cell 'J14' then we would back a formula range '$B122:$B131'. So, in this case if it was the first formula to copy then you should modify some parameters in the formula.

Conclusion:
So, we have four formula, which is copied down. There is no need to copy the entire block of cells as in the example below.

Automatically increase the range by copying blocks of cells

There is another way to show the maximum and minimum values for a given range of cells. In the picture below you see a situation in which copying a block of cells automatically change the range of cells.
Automatically increase the range of the cell block when copying


The formula in cell 'D11' is as follows.
=MAX(B2:B11)

The formula in cell 'E11' is as follows.
=MIN(B2:B11)

Select a block of cells from "D2:D11' and when there is a "cross" click on Fill handle hold the left mouse button and drag down. Automatically changes the range of 10 rows and returns the result for the respective formula.