Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
R1C1 Formula in Excel
I have an RC type formula and I want to dynamically change the end value
based on an entry in A2. I'd prefer to keep IF statements out of it if possible. If A2=1, I want it to be =SUM(RC[9]:RC[9]) If A2=2 I want =SUM(RC[9]:RC[10]) If A2=3, I want =SUM(RC[9]:RC[11]) Suggestions? Thanks, Barb Reinhardt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
R1C1 Formula in Excel
Try =SUM(OFFSET(RC[9],0,0,1,R1C2)). The offset basically defines a range
that begins at RC[9], taking one row and R1C2 columns. "Barb Reinhardt" wrote: I have an RC type formula and I want to dynamically change the end value based on an entry in A2. I'd prefer to keep IF statements out of it if possible. If A2=1, I want it to be =SUM(RC[9]:RC[9]) If A2=2 I want =SUM(RC[9]:RC[10]) If A2=3, I want =SUM(RC[9]:RC[11]) Suggestions? Thanks, Barb Reinhardt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
R1C1 Formula in Excel
you can use the INDIRECT function which turns a text string into a reference.
=SUM(INDIRECT("RC[9]:RC["&8+A2&"]")) "Barb Reinhardt" wrote: I have an RC type formula and I want to dynamically change the end value based on an entry in A2. I'd prefer to keep IF statements out of it if possible. If A2=1, I want it to be =SUM(RC[9]:RC[9]) If A2=2 I want =SUM(RC[9]:RC[10]) If A2=3, I want =SUM(RC[9]:RC[11]) Suggestions? Thanks, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) | |||
How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Formula Integrity Not Preserved During Sort in Excel 2000 | Excel Discussion (Misc queries) |