| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
multiple cell references
If I have in a cell reference =SUM(A3:A300) how to have 3 and 300 referenced to another cell so that I can change range without changing formula? |
|
#2
|
|||
|
|||
|
You could use a defined name for the range, or if you really must you could use INDIRECT =SUM(INDIRECT($B$3)) but possibly you are trying to compensate for a formula that might better be written as =SUM(A$3, OFFSET(A301,-1,0)) if you are concerned about inserting or deleting rows above row 301 and below row 3. http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... If I have in a cell reference =SUM(A3:A300) how to have 3 and 300 referenced to another cell so that I can change range without changing formula? |
|
#3
|
|||
|
|||
|
What I want is to have 3 and 300 (in my example) in another cell as variable
so by changing those inputs I would change the desired range. "David McRitchie" wrote: You could use a defined name for the range, or if you really must you could use INDIRECT =SUM(INDIRECT($B$3)) but possibly you are trying to compensate for a formula that might better be written as =SUM(A$3, OFFSET(A301,-1,0)) if you are concerned about inserting or deleting rows above row 301 and below row 3. http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... If I have in a cell reference =SUM(A3:A300) how to have 3 and 300 referenced to another cell so that I can change range without changing formula? |
|
#4
|
|||
|
|||
|
Sorry I had not tested INDIRECT
=SUM(INDIRECT(L18 & ":" & M18)) where L18: A3 and M18: A300 =SUM(INDIRECT(TEXT(L17,"@"))) where L17: A3:A300 must be a shorter solution using INDIRECT --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... What I want is to have 3 and 300 (in my example) in another cell as variable so by changing those inputs I would change the desired range. "David McRitchie" wrote: You could use a defined name for the range, or if you really must you could use INDIRECT =SUM(INDIRECT($B$3)) but possibly you are trying to compensate for a formula that might better be written as =SUM(A$3, OFFSET(A301,-1,0)) if you are concerned about inserting or deleting rows above row 301 and below row 3. http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... If I have in a cell reference =SUM(A3:A300) how to have 3 and 300 referenced to another cell so that I can change range without changing formula? |
|
#5
|
|||
|
|||
|
Thank you David, it works.
"David McRitchie" wrote: Sorry I had not tested INDIRECT =SUM(INDIRECT(L18 & ":" & M18)) where L18: A3 and M18: A300 =SUM(INDIRECT(TEXT(L17,"@"))) where L17: A3:A300 must be a shorter solution using INDIRECT --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... What I want is to have 3 and 300 (in my example) in another cell as variable so by changing those inputs I would change the desired range. "David McRitchie" wrote: You could use a defined name for the range, or if you really must you could use INDIRECT =SUM(INDIRECT($B$3)) but possibly you are trying to compensate for a formula that might better be written as =SUM(A$3, OFFSET(A301,-1,0)) if you are concerned about inserting or deleting rows above row 301 and below row 3. http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... If I have in a cell reference =SUM(A3:A300) how to have 3 and 300 referenced to another cell so that I can change range without changing formula? |
|
#6
|
|||
|
|||
|
You're welcome.
This is a bit better the A1 is to indicate type of reference A1 or R1C1 syntax: INDIRECT(ref_text,a1) =SUM(INDIRECT(L1, True) where L1: 'A3:A300 since True is the default and is for A1 style reference, I don't know why this originally failed when I tested, perhaps I had a leading space in the L1 value =SUM(INDIRECT(L1)) I will update my indirect.htm web page accordingly http://www.mvps.org/dmcritchie/excel/indirect.htm and test my replies a bit more often as well. --- David "Branko" wrote in message ... Thank you David, it works. "David McRitchie" wrote: Sorry I had not tested INDIRECT =SUM(INDIRECT(L18 & ":" & M18)) where L18: A3 and M18: A300 =SUM(INDIRECT(TEXT(L17,"@"))) where L17: A3:A300 must be a shorter solution using INDIRECT --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... What I want is to have 3 and 300 (in my example) in another cell as variable so by changing those inputs I would change the desired range. "David McRitchie" wrote: You could use a defined name for the range, or if you really must you could use INDIRECT =SUM(INDIRECT($B$3)) but possibly you are trying to compensate for a formula that might better be written as =SUM(A$3, OFFSET(A301,-1,0)) if you are concerned about inserting or deleting rows above row 301 and below row 3. http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Branko" wrote in message ... If I have in a cell reference =SUM(A3:A300) how to have 3 and 300 referenced to another cell so that I can change range without changing formula? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| apply cell names to formulas in multiple worksheets | Excel Worksheet Functions | |||
| Other than the cell not being wide enough, what do the multiple #. | Excel Discussion (Misc queries) | |||
| Re What is the formula for adding multiple numbers in a cell | Excel Discussion (Misc queries) | |||
| Using the MAX function with "constant increment" cell references | Excel Worksheet Functions | |||
| Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) | |||
Linear Mode
