Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Hi all;
Hopefully a simple problem... In Excel 97 I want to sum integer fields in a column, where the 'sum' field is the last item in the column. Say, 22 integers in column D, and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting rows above the cell containing the =SUM() entry does NOT always adjust the range in the SUM parentheses correctly. Depends how you insert rows, and where the inserting point is, and possibly on other factors. Net result is that I can insert rows in the sheet which do not get included correctly in the column sum. I've seen this inject serious defects into many spreadsheets. It bit me again today. I need to generate a formula which says "sum column D, starting at cell $D6 and going through to (the cell above the sum cell)". Excel Help says I can do this using RC[-1] notation, but the SUM function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula you typed contains an error". Web search didn't help. Lots of tutorials and examples, nothing on this. Can anyone help please ? Thanks, Mal. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make them work on the Each cell Value | Excel Worksheet Functions | |||
r1c1 reference style - how to make it default | Excel Discussion (Misc queries) | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
How can i make this work within an IF statement: (41<=Cell<=46) | New Users to Excel | |||
Macro to make a cell work like a check | Excel Programming |