Excel Define a range based on a cell value

0 votes
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 by Kithuzzz
• 38,010 points
2,844 views

1 answer to this question.

0 votes

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.

answered Nov 8, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

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

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

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
327 views
0 votes
1 answer

Excel Conditional Formatting based on Adjacent Cell Value

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

answered Oct 10, 2022 in Others by narikkadan
• 63,700 points
1,547 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

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

answered Oct 20, 2022 in Others by narikkadan
• 63,700 points
1,189 views
0 votes
1 answer

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

answered Dec 25, 2022 in Others by narikkadan
• 63,700 points
548 views
0 votes
1 answer

How to use a named column in Excel formulas

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

answered Oct 24, 2022 in Others by narikkadan
• 63,700 points
366 views
0 votes
1 answer

Control cell value from another cell in Excel

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

answered Nov 10, 2022 in Others by narikkadan
• 63,700 points
1,136 views
0 votes
1 answer

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

answered Nov 11, 2022 in Others by narikkadan
• 63,700 points
1,138 views
0 votes
1 answer

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

answered Dec 10, 2022 in Others by narikkadan
• 63,700 points
1,653 views
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,700 points
537 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,700 points
1,241 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP