Sum(indirect

[ron.bo]

Does anyone know of a routine or a way to change a number of formulas from SUM to SUM(INDIRECT ?

It seems to me that this would be a fairly common problem, but I can not find a solution.

Thanks for any help.

 

[GlennUK]

And what do your full formulas look like? I can't tell if Edit/Replaces are going to work, as you've given no examples.

 

[jim may]

Check out Cell D2

Excel Workbook
	A	B	C	D	E	F	G	H
1	1.00							Range Name
2	2.00		The Total	35		The Sum:		Assigned
3	3.00					Start Line:	5	SRow
4	4.00					End Line:	9	ERow
5	5.00
6	6.00
7	7.00
8	8.00
9	9.00
10	10.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=SUM(INDIRECT("A"&SRow&":A"&ERow))

Excel Workbook
Name	Refers To
ERow	=Sheet1!$G$4
SRow	=Sheet1!$G$3
Workbook Defined Names

 

[ron.bo]

I use find/replace for simple formulas but in the following example find/replace gets to be a lot of work.

<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=80 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; COLOR: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl41 height=17 width=80 align=right x:num="-2.6975149957155099E-2"></TD></TR></TBODY></TABLE>
IF($C6=0,"",(SUM(C6:N6)-SUM(O6:Z6))/SUM(O6:Z6))

 

[GlennUK]

I use find/replace for simple formulas but in the following example find/replace gets to be a lot of work.

<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=80 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; COLOR: red; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl41 height=17 width=80 align=right x:num="-2.6975149957155099E-2"></TD></TR></TBODY></TABLE>
IF($C6=0,"",(SUM(C6:N6)-SUM(O6:Z6))/SUM(O6:Z6))

Yeah, that's about 4 times the amount of work. But it still might be easier than typing into each cell by hand. It depends whether there's a good set of characters to match the end of the SUM function argument. Like, in your example, the 6) is a good place to start replacing things, so I'd go:
Edit/Replace:
= with #
6) with 6"))
SUM( with SUM(INDIRECT("
# with =

That's the best I can offer in native Excel.