Is it possible to set a range's parameters based on a cell's value?

As an illustration, my choice is A1:A5. That's five cells, then. Can Excel make this determination by setting a cell value (say B1) to 5?

With just one modification in a cell value, it is designed to quickly change a large number of ranges. Thus, if I were to set cell value (B1) to 6, A1:A6 would be the new default range.

To be more precise, I want to do it the other way around.
Nov 8, 2022 in Others 2,152 views

## 1 answer to this question.

Let's say that cells A1, A2, A3, A4, and A5 all contain the numbers 1, 2, 3, and 5, respectively. We compute the total of A1:Ax in cell A7. In cell B1, x is mentioned (in this case, x can be any number from 1 to 6). You can enter the formula below in cell A7:

`=SUM(A1:INDIRECT(CONCATENATE("A",B1)))`

CONCATENATE will give you the index of the cell Ax(if you put 3 in B1, CONCATENATE("A",B1)) gives A3).

INDIRECT convert "A3" to an index.

• 63,180 points

## Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

## Excel Conditional Formatting based on Adjacent Cell Value

The row number used in the formula ...READ MORE

## Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

## Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

## How to use a named column in Excel formulas

Let's say I have the following figures ...READ MORE

## Control cell value from another cell in Excel

You may accomplish this by using two ...READ MORE

## Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

## Use python to remove blank cells from column and shift populated cells up

Use pandas to import and modify your excel. import pandas ...READ MORE