Home |
Search |
Today's Posts |
#1
|
|||
|
|||
The reference changes when I insert a row?!?! Help
I've created a worksheet that uses a macro. The macro adds a line at the top
of the worksheet. I have some cells that total from different columns. I thought that changing the formula in the totals cells to an absolute reference should work...I want the formula in the total box to always add the cells from D9 on down the line...In my totals cell I wrote =sum($D$9:$D$500). When I run the macro, it moves all of my rows down 1 row and inserts a new row 9. When I click on my totals cell to read the formula, it now reads =sum($D$10:$D$501). HELPPPPP! |
#2
|
|||
|
|||
"ntscott" wrote: I've created a worksheet that uses a macro. The macro adds a line at the top of the worksheet. I have some cells that total from different columns. I thought that changing the formula in the totals cells to an absolute reference should work...I want the formula in the total box to always add the cells from D9 on down the line...In my totals cell I wrote =sum($D$9:$D$500). When I run the macro, it moves all of my rows down 1 row and inserts a new row 9. When I click on my totals cell to read the formula, it now reads =sum($D$10:$D$501). HELPPPPP! Why not get the macro to insert a formula. If the total goes in d8 add the line range("D8").formula = "=sum(D9:D500)" Adding the extra rows will make the formula future proof. Regards Peter |
#3
|
|||
|
|||
This formula will *always* start your range at D9:
=SUM(INDIRECT("$D$9"):$D$500) Note: Inserting rows will extend the range downwards. Does that help? Ron |
#4
|
|||
|
|||
Start your sum from one row above, ie row 8 and make that row blank (maybe
shrink it a bit too) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "ntscott" wrote in message ... I've created a worksheet that uses a macro. The macro adds a line at the top of the worksheet. I have some cells that total from different columns. I thought that changing the formula in the totals cells to an absolute reference should work...I want the formula in the total box to always add the cells from D9 on down the line...In my totals cell I wrote =sum($D$9:$D$500). When I run the macro, it moves all of my rows down 1 row and inserts a new row 9. When I click on my totals cell to read the formula, it now reads =sum($D$10:$D$501). HELPPPPP! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel | |||
How can I insert a cell reference in a footer (eg for variable foo | Excel Discussion (Misc queries) | |||
How can I insert a cell reference in a footer (eg for variable foo | Excel Discussion (Misc queries) |