Editing Formulas with a Macro

**adr150** · 02-17-2010, 07:19 PM

I'm trying to extend the range of a summation formula with a macro.

I've attached a dummy worksheet. I've named each cell in row 8 as well as the summation ranges they are calculating.

I am looking for the result of the macro to have each total's formula range to extend to to row 7 of its respective column.

Any help with this is greatly appreciated!

Andrew

**JBeaucaire** · 02-17-2010, 07:23 PM

Simplest answer...

1) Move your "totals" to row 1, above the data being evaluated.
2) Change you FY10Range so that it refers to =Sheet1!$D$4:$D$1000
...etc.

Next simplest

1) Click on your data and press CTRL-L to activate the LIST function
2) Now each time you add to the bottom of the list, your ranges should expand themselves
3) Totals can stay below the data, outside the list range

**tony h** · 02-17-2010, 07:29 PM

it is quite easy to do but why?

If you are inserting rows the sum formula should extend anyway.

**adr150** · 02-17-2010, 07:57 PM

while it has some awkward side effects (namely the blue border and seemingly slowing excel a bit),using the list works quite well. Thanks!

tony h:
The reason I'm asking is because I'm using a macro to insert rows and (before finding out that a list wold work) the sum formula wouldn't follow the new rows.

**adr150** · 03-05-2010, 01:08 PM

Originally Posted by adr150

while it has some awkward side effects (namely the blue border and seemingly slowing excel a bit),using the list works quite well. Thanks!

Well, I've run into a problem with using a list. the data in the list is linked to another tab. The file in question will need to be replicated many times, but with slightly different information. Additionally, when I replicate the file, I sometimes need to copy the tab with the list and the tab that it is linked to within the same workbook.

This is where the problem comes in: when you select both tabs, right click on the tab, and choose 'move or copy' and try to make a copy within the workbook, an error pops up saying "You cannot copy or move a group of sheets that contain a list."

JBeaucaire suggested:

1) Move your "totals" to row 1, above the data being evaluated.
2) Change you FY10Range so that it refers to =Sheet1!$D$4:$D$1000
...etc.

The problem with this solution is that below the first range is a second range that cannot be moved. So extending the formula down many rows isn't feasible.

Any other suggestions? Can this be solved with a macro?

Thanks again for the help!

**JBeaucaire** · 03-05-2010, 02:01 PM

Let's see if we can set a defined dynamic range, still better than macros, IMO.
Perhaps change the REFERS TO reference for the FY10Range to:

=INDIRECT("Sheet1!$D$4:" & CELL("address",OFFSET(FY10Forecast,-1,0)))

Or even better, since I can already see the next question coming regarding how to do this same thing in the next section below this one...

=INDIRECT("Sheet1!" & INDEX(Sheet1!$D:$D, MATCH("2010 Forecast",Sheet1!$D:$D, 0) & ":" & CELL("address",OFFSET(FY10Forecast,-1,0))))

For this to work, you cannot use the exact same column header titles in each table in the same column, at least one character needs to be different.

**adr150** · 03-08-2010, 08:37 AM

for some reason the indirect function isn't working on my computer. is there a setting that I need to adjust? just doing a simple =indirect(d4,false) returns #REF!

**adr150** · 03-08-2010, 10:33 AM

ahh got the basic indirect formula to work. didnt realize you need quotes around the cell reference.

however, the formula
=INDIRECT("Sheet1!$D$4:" & CELL("address",OFFSET(FY10Forecast,-1,0)))

doesn't seem to be working as it returns #VALUE!

**JBeaucaire** · 03-08-2010, 12:56 PM

Try this in D8:

=SUM(INDIRECT("D4:" & CELL("address",OFFSET(FY10Forecast,-1,0))))

**adr150** · 03-08-2010, 01:51 PM

awesome, thanks!

I actually just figured it out myself. I used a slightly different configuration, but the result seems to be the same

here's what I used
=SUM(INDIRECT("d4"):INDIRECT(CELL("address",OFFSET(FY10Forecast,-1,0))))

anyway, this has been an interesting problem. i wasn't aware of the INDIRECT, CELL, or OFFSET functions before. a couple more tools for the toolbelt

thanks!