Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|