Excel Define a range based on a cell value

Question

Is it possible to define a range based on a value given in a cell    So  for example  My selection is A1 A5 That are five cells  is it possible to let excel determine this by setting a cell value  like B1  to 5    It for the purpose of easily changes a lot of ranges with one change in a cell value  So if I would change the cell value  B1  to 6  The range would automatically change to A1 A6  Even more specific  I would like to do it reversed    Final example  Selection should be A6 A10  this are the five cells   In B1 I have the value 5  If I change the value of B1 to 6  The range should change to A5 A10  Could somebody help me

User · Answer

You can also use OFFSET   OFFSET  A 10 - B 1 1 0  B 1    It moves the range  A 10 up by  B 1-1  becomes  A 6   A 5   and then resizes the range to  B 1 rows  becomes  A 6  A 10   A 5  A 10

User · Answer

Say you have number 1 2 3 4 5 6  in cell A1 A2 A3 A4 A5 A6 respectively  in cell A7 we calculate the sum of A1 Ax  x is specified in cell B1  in this case  x can be any number from 1 to 6   in cell A7  you can write the following formular    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 a index   see this link Using the value in a cell as a cell reference in a formula

User · Answer

This should be close to what you are looking for your first example    SUM INDIRECT  A1 A  amp B1 TRUE     This should be close to what you are looking for your final example    SUM INDIRECT  A  amp 1 B1 amp   A  amp B1 TRUE

User · Answer

Here is an option   It works by using making an INDIRECT ADDRESS       from the ROW and COLUMN of the start cell  A1  down to the initial row   the number of rows held in B1   SUM INDIRECT ADDRESS ROW A1  COLUMN A1    INDIRECT ADDRESS ROW A1  B1 COLUMN A1       A1  is the start of data in a the  A  column  B1  is the number of rows to sum

User · Answer

Old post but this is exactly what I needed  simple question  how to change it to count column rather than Row  Thankyou in advance  Novice to Excel    SUM A1 INDIRECT CONCATENATE  A  C5      I e My data is A1 B1 C1 D1 etc  rather then A1 A2 A3 A4

User · Answer

Based on answer by  Cici I give here a more generic solution    SUM INDIRECT CONCATENATE B1 C1   INDIRECT CONCATENATE B2 C2     In Italian version of Excel    SOMMA INDIRETTO CONCATENA B1 C1   INDIRETTO CONCATENA B2 C2     Where B1-C2 cells hold these values    A  1 A  5   You can change these valuese to change the final range at wish     Splitting the formula in parts    SUM INDIRECT CONCATENATE B1 C1   INDIRECT CONCATENATE B2 C2    CONCATENATE B1 C1  - result is A1  INDIRECT CONCATENATE B1 C1   -   result is reference to A1   Hence    SUM INDIRECT CONCATENATE B1 C1   INDIRECT CONCATENATE B2 C2     results in   SUM A1 A5     I ll write down here a couple of SEO keywords for Italian users    come creare dinamicamente l indirizzo di un intervallo in excel formula per definire un intervallo di celle in excel    Con la formula indicata qui sopra basta scrivere nelle caselle da B1 a C2 gli estremi dell intervallo per vedelo cambiare dentro la formula stessa

[excel] Excel Define a range based on a cell value

Examples related to excel

Examples related to range

Examples related to cell