In this article, we will be discussing the “Excel Dynamic range Based on Cell Value”. Here, we will be creating dynamic ranges without selecting them based on some cell value. We will be performing some calculations to see how his thing actually works.
Using the INDIRECT & CONCATENATE Function to create Dynamic Range
Let`s say we have a column containing some numbers. We want to perform a sum calculation based on a cell value. In a specific cell of our worksheet, we will be indicating the number of cells of that specified column. Based on the cell value the calculation will be done. Like, if we write 2 in that specific cell, the formula which we will be using will calculate the sum of the 1st two cells of the column. We will be using the INDIRECT and CONCATENATE functions together. The procedure is shown below.
- First, in column B put some numbers. Make two heading Cell Value & SUM in Cell C1 and Cell D1 accordingly.
- IN C2 type a number say 4 and type this below formula in D2:
=SUM(<span style="color: #3366ff;">B1</span>:INDIRECT<span style="color: #993300;">(</span>CONCATENATE<span style="color: #800080;">(</span>"B",<span style="color: #ff0000;">C2</span><span style="color: #800080;">)</span><span style="color: #ff0000;">)</span>)
- After pressing Enter you will see the below result.
- Here the formula calculates the sum of the first 4 cells of column B. By changing the value of cell C2, the result will also change.
Read More: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)
Similar Readings
- Data Validation Drop Down List with Excel Table Dynamic Range
- How to Create Dynamic Range Using Excel INDEX Function
- Create a Dynamic Chart Range in Excel (2 Methods)
- Excel VBA: Dynamic Range Based on Cell Value (3 Methods)
- How to Use Dynamic Range in Excel VBA (11 Suitable Ways)
Choosing Cells Dynamically in a Column Based on Two cell Values (Using INDIRECT Function)
This example is similar to the previous one. The only difference here is that we will be choosing the range of column B based on 2 cell values. Cell C2 will select the first cell of the dynamic range and cell C3 will define the last cell of the range. We will be performing the same calculation. The formula will be different and will not contain the CONCATENATE function. The process is shown below.
- First, in column B put some numbers. Make two heading Cell Value & SUM in Cell C1 and Cell D1 accordingly.
- IN C2 type 2 and in C3 type 6. Type the formula
=SUM(INDIRECT<span style="color: #ff0000;">(</span>"B"&<span style="color: #3366ff;">C2</span>&":B"&<span style="color: #ff0000;">C3)</span>)in cell D2.
- After pressing Enter you will see the below result.
- Here the formula calculates the number from cell B2 to B6. By changing the numbers, you will see the result changes.
Read More: Dynamic Range for Multiple Columns with Excel OFFSET
Choosing Cells Dynamically Based on Two cell Values (Using OFFSET Function)
The OFFSET function can be used to make a range of cells dynamic. Here we will select the number of cells and columns separately to perform the sum calculation. The procedure is given below.
- Let`s say we have 2 sets of numbers in Column B and Column C. Make 3 headers titled Cell Value, Column, and SUM.
- In E2 type 7 and in F2 type 2. Write down the formula
=SUM(OFFSET<span style="color: #ff0000;">(</span><span style="color: #3366ff;">B1</span>,0,0,<span style="color: #ff0000;">E2</span>,<span style="color: #800080;">F2</span><span style="color: #ff0000;">)</span>)in cell G2 and press Enter.
- After pressing Enter you will get to see the below result.
- Here the formula first starts the calculation from B1 and comes to cell B7 because the height of the offset function is given as E2 which contains the value 7. As the width is given as 2, so the whole range of calculation will be B1:C7.
Read More: OFFSET Function to Create & Use Dynamic Range in Excel
Conclusion
In this article, we analyzed Excel dynamic range. We create the dynamic ranges using some formulas. The calculation of those dynamic ranges was done based on some cell values. Hope you will love this article.
Stay tuned for more useful articles.
Further Readings
- How to Create Dynamic Named Range in Excel (2 Easy Ways)
- Autofill Dynamic Range Using VBA in Excel
- How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)
- Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)
- How to Create a Range of Numbers in Excel (3 Easy Methods)
- Dynamic Named Range Based on Cell Value in Excel
