Indirect Function and Sum
 

I am having a bit of a problem. I have multiple worksheets that are all
setup the same. The first sheet is called "Start" and the last is called
"End". These are "templates". The data is on the sheets in between. I
have a formula that works just fine as is.
=Sum(Start:End!B1)
This sums all the "B1" cells in all the sheets from Start to End.

Here is the problem. I would like to change the row 1 to a variable that i
type into a an "input" cell. Then have that cell used in the current
formula so I can change what cell is being summed w/o having to physically
change the formula. I can't seem to get it to work. I've tried =Sum
(Indirect("Start:End!B" & A1) but to no availe.

Can anyone help out on this.

---
thanks in advance
gr

From help: If ref_text is not a valid cell reference, INDIRECT returns the
#REF! error value.

So, apparently, the 3D part is not a valid cell reference.

To overcome this limitation, you could use the worksheet change event. Some
example code (see below) will change the formula in cell A8 to sum the
values in the row number entered in cell A1. Of course, you will need to
change the cell references to reflect where you actually have the formula
entered.

Copy the code, right click on the sheet tab (of the sheet where you have the
formula and want to enter the row number into cell A1), select "View Code"
and paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Application.EnableEvents = False
Range("A8").Formula = "=SUM(Start:End!B" & Target.Value & ")"
Application.EnableEvents = True
End Sub


"gr" wrote in message
...
I am having a bit of a problem. I have multiple worksheets that are all
setup the same. The first sheet is called "Start" and the last is called
"End". These are "templates". The data is on the sheets in between. I
have a formula that works just fine as is.
=Sum(Start:End!B1)
This sums all the "B1" cells in all the sheets from Start to End.

Here is the problem. I would like to change the row 1 to a variable that
i
type into a an "input" cell. Then have that cell used in the current
formula so I can change what cell is being summed w/o having to physically
change the formula. I can't seem to get it to work. I've tried =Sum
(Indirect("Start:End!B" & A1) but to no availe.

Can anyone help out on this.

---
thanks in advance
gr

A dirty trick (using the Excel V4 function EVALUATE(), see
http://www.sulprobil.com/html/it_quality.html):

Enter your desired row number let's say into cell B1.

Enter into cell B2: ="=SUM(Start:End!B"&B1&")"

Enter into cell B3: =EVALC

Define the name EVALC with the value: =EVALUATE(INDIRECT
("$B$2"))

[insert / name / define...]

HTH,
Bernd