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 in Others by Kithuzzz
• 20,660 points
42 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 by narikkadan
• 37,660 points

Related Questions In Others

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 in Others by narikkadan
• 37,660 points
44 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

answered Oct 17 in Others by narikkadan
• 37,660 points
47 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 in Others by narikkadan
• 37,660 points
43 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 in Others by narikkadan
• 37,660 points
40 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 in Others by narikkadan
• 37,660 points
37 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 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 in Others by narikkadan
• 37,660 points
56 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 in Others by narikkadan
• 37,660 points
179 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