| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defining a range using a cell reference
Hi all, I am trying to define a range but the range needs to be dynamic. Instead of simply defining the range as (A1:A15) I need to express my range as (A1: "A1+the value in another cell, A20). Please could you let me know how to achieve this. Thanks! Jag -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=548517 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Defining a range using a cell reference
Hi
Try =INDIRECT("A1:A"&A20) -- Regards Roger Govier "jagbabbra" wrote in message ... Hi all, I am trying to define a range but the range needs to be dynamic. Instead of simply defining the range as (A1:A15) I need to express my range as (A1: "A1+the value in another cell, A20). Please could you let me know how to achieve this. Thanks! Jag -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=548517 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Defining a range using a cell reference
thank you for the suggestion but this seems to only work if you refer to a cell that has an address in ... eg if you use indirect the cell you refer to must contain (A11) or something to that effect. I have a cell that contains a number and would like to use that to define how many columns the range must cover.Eg If i start my range at B2 and my reference cell (lets call that A20) has the number 3 in then my range should be B2 : D2 Please can you shed some light on this, cheers -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=548517 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Defining a range using a cell reference
Hi
With a value of 15 in cell A20, the range would be A1:A15 and the formula =SUM(INDIRECT("A1:A"&A20)) for example would sum all of the values within this range. From your first description, it sounded as though this was what you wanted.. From what you now describe you could use =OFFSET(B2,,,1,A20) as this would refer to the range B2:B4 -- Regards Roger Govier "jagbabbra" wrote in message ... thank you for the suggestion but this seems to only work if you refer to a cell that has an address in ... eg if you use indirect the cell you refer to must contain (A11) or something to that effect. I have a cell that contains a number and would like to use that to define how many columns the range must cover.Eg If i start my range at B2 and my reference cell (lets call that A20) has the number 3 in then my range should be B2 : D2 Please can you shed some light on this, cheers -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=548517 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Defining a range using a cell reference
B2:INDEX(B2:IV2,A20)
jagbabbra wrote: thank you for the suggestion but this seems to only work if you refer to a cell that has an address in ... eg if you use indirect the cell you refer to must contain (A11) or something to that effect. I have a cell that contains a number and would like to use that to define how many columns the range must cover.Eg If i start my range at B2 and my reference cell (lets call that A20) has the number 3 in then my range should be B2 : D2 Please can you shed some light on this, cheers -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=548517 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Defining a range using a cell reference
Much nicer non-volatile solution!
-- Regards Roger Govier wrote in message ups.com... B2:INDEX(B2:IV2,A20) jagbabbra wrote: thank you for the suggestion but this seems to only work if you refer to a cell that has an address in ... eg if you use indirect the cell you refer to must contain (A11) or something to that effect. I have a cell that contains a number and would like to use that to define how many columns the range must cover.Eg If i start my range at B2 and my reference cell (lets call that A20) has the number 3 in then my range should be B2 : D2 Please can you shed some light on this, cheers -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=548517 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Reference with Range Name | Excel Worksheet Functions | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Nested IF statement with cell range reference | Excel Worksheet Functions | |||
| How do I change a cell range with a reference cell? | Excel Discussion (Misc queries) | |||
| use a cell to reference a range in a vlookup | Excel Discussion (Misc queries) | |||
Linear Mode
