Hello All,
I built a user defined cost roll-up function to calculate the total
cost for all Parts on a BOM (Bill of Materials) at each level. See
the function below.

The function works great and I have been able to get it to auto update
using the "Application.Volatile" command when data is changed within
the sheet in the workbook, however when data is changed in another
sheet in the same workbook it fails force the function to
recalculate. This forces me to manually navigate to the sheet and hit
Ctrl-Alt-Shift-F9 to get the entire sheet to recalculate.

Is there a global workbook wide Volatile command that will force cells
to recalculate across different sheets in a workbook or even across
different excel files?

Thanks in advance.


Excel VB Function:
Function SumLowerLevel(Level As Range, answerLoc As Range) As Currency
Application.Volatile 'Forces Excel to recalcuate all values when a
cell changes

'================================================= ========'
'Generates sum of all values in "answerLoc"
'for all rows where the "Level" is one increment higher
'================================================= ========'

'Variables
Dim row, col, currentLevel As Integer
Dim Sum As Double
Sum = 0

'set starting summation row and column cell locations based on input
level locations
row = Level.row + 1
col = Level.Column

'set current row level based on input level value
currentLevel = Level.Value2

'set the cell to gather the sums from, should be the same and return
location
colTotal = answerLoc.Column

'interate over all rows below current
Do While (Cells(row, col) currentLevel)
' if the row's level is one greater then add to sum
If (Cells(row, col) = currentLevel + 1) Then
'Check if data is valid
If (Application.WorksheetFunction.IsNA(Cells(row, colTotal)) =
False) Then
Sum = Sum + Cells(row, colTotal)
End If
End If
'increment the row and loop
row = row + 1
Loop

'return total sum
SumLowerLevel = Sum
End Function

Application.volatile means that the function will recalculate when excel
recalculates--not when a cell changes. (Not all changes cause a recalculation.)

If you really want it to know when it should recalculate, you should pass it all
the ranges that it needs in the function--not just level and answerloc.



wrote:

Hello All,
I built a user defined cost roll-up function to calculate the total
cost for all Parts on a BOM (Bill of Materials) at each level. See
the function below.

The function works great and I have been able to get it to auto update
using the "Application.Volatile" command when data is changed within
the sheet in the workbook, however when data is changed in another
sheet in the same workbook it fails force the function to
recalculate. This forces me to manually navigate to the sheet and hit
Ctrl-Alt-Shift-F9 to get the entire sheet to recalculate.

Is there a global workbook wide Volatile command that will force cells
to recalculate across different sheets in a workbook or even across
different excel files?

Thanks in advance.

Excel VB Function:
Function SumLowerLevel(Level As Range, answerLoc As Range) As Currency
Application.Volatile 'Forces Excel to recalcuate all values when a
cell changes

'================================================= ========'
'Generates sum of all values in "answerLoc"
'for all rows where the "Level" is one increment higher
'================================================= ========'

'Variables
Dim row, col, currentLevel As Integer
Dim Sum As Double
Sum = 0

'set starting summation row and column cell locations based on input
level locations
row = Level.row + 1
col = Level.Column

'set current row level based on input level value
currentLevel = Level.Value2

'set the cell to gather the sums from, should be the same and return
location
colTotal = answerLoc.Column

'interate over all rows below current
Do While (Cells(row, col) currentLevel)
' if the row's level is one greater then add to sum
If (Cells(row, col) = currentLevel + 1) Then
'Check if data is valid
If (Application.WorksheetFunction.IsNA(Cells(row, colTotal)) =
False) Then
Sum = Sum + Cells(row, colTotal)
End If
End If
'increment the row and loop
row = row + 1
Loop

'return total sum
SumLowerLevel = Sum
End Function

--

Dave Peterson