| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula should retain
Hi, In A1 I have formula sum(A3:A12), however when I insert a line in the range, the formula changes to sum(A4:A13), however I would like the formula to stay the same. An idea is sum(Indirect("A3:A12")), however this isnt very flexible. Many Thanks -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=558787 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
Try sum(A$3:A$12)
Regards, Stefi €˛T De Villiers€¯ ezt Ć*rta: Hi, In A1 I have formula sum(A3:A12), however when I insert a line in the range, the formula changes to sum(A4:A13), however I would like the formula to stay the same. An idea is sum(Indirect("A3:A12")), however this isnt very flexible. Many Thanks -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=558787 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
Ive tried this before, unfortunately doesn't work -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=558787 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
It isn't, but it is the only way (at least that I know of).
What specifically is not flexible about it, there may some tweaks to use? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "T De Villiers" wrote in message news:T.De.Villiers.2aipgz_1152179104.2158@excelfor um-nospam.com... Hi, In A1 I have formula sum(A3:A12), however when I insert a line in the range, the formula changes to sum(A4:A13), however I would like the formula to stay the same. An idea is sum(Indirect("A3:A12")), however this isnt very flexible. Many Thanks -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=558787 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
T De Villiers wrote:
Hi, In A1 I have formula sum(A3:A12), however when I insert a line in the range, the formula changes to sum(A4:A13), however I would like the formula to stay the same. An idea is sum(Indirect("A3:A12")), however this isnt very flexible. Many Thanks Are you sure it changes as you said? I would have expected it to change to SUM(A3:A13) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
Hi, When I mean sum(indirect("a3:a12")) is inflexible, if I copy it acros it stays as sum(indirect("a3:a12")), as opposed to sum(indirect("b3:b12")) Mnay Thank -- T De Villier ----------------------------------------------------------------------- T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647 View this thread: http://www.excelforum.com/showthread.php?threadid=55878 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
Thought it might be something like that. Try
=SUM(OFFSET(INDIRECT(CHAR(64+MIN(COLUMN(A1)))&"3") ,,,10,1)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "T De Villiers" wrote in message news:T.De.Villiers.2ais90_1152182702.9214@excelfor um-nospam.com... Hi, When I mean sum(indirect("a3:a12")) is inflexible, if I copy it across it stays as sum(indirect("a3:a12")), as opposed to sum(indirect("b3:b12")) Mnay Thanks -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=558787 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula should retain
Thanks Bob, thats excellent -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=558787 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Move Column Retain Formula | Excel Discussion (Misc queries) | |||
| How to delete/change the formula of a cell and yet retain its cont | Excel Worksheet Functions | |||
| retain formula in additional workbooks | New Users to Excel | |||
| Can I retain a formula in the cell pasted to? | Excel Worksheet Functions | |||
| Is there a way to retain a formula while performing cut/paste? | Excel Worksheet Functions | |||
![T De Villiers[_29_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
