| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute Values and Column Insertions
I've created a formula in column A that adds up some cells in column D. Ex:
=sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Absolute Values and Column Insertions
Use the INDIRECT() function.
Like in =sum("$D$3:$D$6") -- Kind regards, Niek Otten Microsoft MVP - Excel "BobK" wrote in message ... I've created a formula in column A that adds up some cells in column D. Ex: =sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Absolute Values and Column Insertions
Sorry!
Like in =SUM(INDIRECT("$D$3:$D$6")) -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... Use the INDIRECT() function. Like in =sum("$D$3:$D$6") -- Kind regards, Niek Otten Microsoft MVP - Excel "BobK" wrote in message ... I've created a formula in column A that adds up some cells in column D. Ex: =sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| #Value Problem - Inconsistent Formulas After Column Insertions | Excel Worksheet Functions | |||
| Summing a Column - Net and Absolute Values | Excel Worksheet Functions | |||
| Name ranges and insertions to spreadsheet to update charts | Charts and Charting in Excel | |||
| Excel insertions (drawings, word art, pics, etc) are not visible. | Excel Discussion (Misc queries) | |||
| sum absolute values of a column of numbers in Excel | Excel Worksheet Functions | |||
Linear Mode
