mrmattmc
11-09-2014, 10:32 PM
I have been doing a bit of searching and I think, thanks to excels method of handling calculations there is not much to be found on this topic.
I have a sheet that is a summary of a few big sheets of information. The summary sheet holds names in the columns and dates in the rows. The primary workhorse for the sheet is the array formula below, in the columns. With 84 columns and 92 rows I end up with 8148 iterations of this formula. (not PHP)
=IFERROR(HYPERLINK(CELL("address",INDEX('T2'!$B$4:$B$1999,MATCH(1,IF('T2'!$K$4:$K$1999=D$3,IF('T2'!$B$4:$B$1 999=$C6,1))),0)),COUNTIFS('T2'!$V$4:$V$1999,$A$1,'T2'!$K$4:$K$1999,D$3,'T2' !$B$4:$B$1999,$C6,'T2'!$E$4:$E$1999,IF($A$3="ALL","*",$A$3),'T2'!$H$4:$H$1999,IF($A$4="ALL","*",$A$4))),0)
I need to take control of what, when and where excel calculates.
The names displayed change based on a team selection in a data validation list
The dates change in a similar way from a data validation list.
both of the above data sets grow/shrink with the selection made.
With this structure I am using this autofilter vba to essentially hide any row where the date value is zero. One choice yields 38 unique dates, where another yields 92 unique dates.
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheet1
Application.ScreenUpdating = False
.AutoFilterMode = False
Range("c5:c61").AutoFilter Field:=1, Criteria1:=">0"
I am using this vba to hide the columns where the name field is zero. This data set shrinks and grows like a champ.
If Range("AQ3").Value = "0" Then
Range("aq3,ar3,as3").EntireColumn.Hidden = True
End If
What I would like to do is detect all "hidden" cells in my range. (A1:CK106) then pass the .Calculate code to all cells that are not hidden as needed using Worksheet_Change. Naturally I would have the entire workbook just set to manual calculation so I could fire the calculate command as needed. Been toying with the idea of maybe changing the "status" of the hidden cells to "Data Tables" and setting the workbook to calculate automatic except for data tables option.
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
I already control calculations for specific events throughout the workbook with
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
[CODE][ActiveSheet.Calculate/CODE]
Any help would be appreciated.
I have a sheet that is a summary of a few big sheets of information. The summary sheet holds names in the columns and dates in the rows. The primary workhorse for the sheet is the array formula below, in the columns. With 84 columns and 92 rows I end up with 8148 iterations of this formula. (not PHP)
=IFERROR(HYPERLINK(CELL("address",INDEX('T2'!$B$4:$B$1999,MATCH(1,IF('T2'!$K$4:$K$1999=D$3,IF('T2'!$B$4:$B$1 999=$C6,1))),0)),COUNTIFS('T2'!$V$4:$V$1999,$A$1,'T2'!$K$4:$K$1999,D$3,'T2' !$B$4:$B$1999,$C6,'T2'!$E$4:$E$1999,IF($A$3="ALL","*",$A$3),'T2'!$H$4:$H$1999,IF($A$4="ALL","*",$A$4))),0)
I need to take control of what, when and where excel calculates.
The names displayed change based on a team selection in a data validation list
The dates change in a similar way from a data validation list.
both of the above data sets grow/shrink with the selection made.
With this structure I am using this autofilter vba to essentially hide any row where the date value is zero. One choice yields 38 unique dates, where another yields 92 unique dates.
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheet1
Application.ScreenUpdating = False
.AutoFilterMode = False
Range("c5:c61").AutoFilter Field:=1, Criteria1:=">0"
I am using this vba to hide the columns where the name field is zero. This data set shrinks and grows like a champ.
If Range("AQ3").Value = "0" Then
Range("aq3,ar3,as3").EntireColumn.Hidden = True
End If
What I would like to do is detect all "hidden" cells in my range. (A1:CK106) then pass the .Calculate code to all cells that are not hidden as needed using Worksheet_Change. Naturally I would have the entire workbook just set to manual calculation so I could fire the calculate command as needed. Been toying with the idea of maybe changing the "status" of the hidden cells to "Data Tables" and setting the workbook to calculate automatic except for data tables option.
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
I already control calculations for specific events throughout the workbook with
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
[CODE][ActiveSheet.Calculate/CODE]
Any help would be appreciated.