Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gr
 
Posts: n/a
Default 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
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #3   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"