View and Cell Sum Range

[Guest]

I am looking for a way to change the view i.e. change hidden columns
representing months and change the cell range sum within a totals column
depending on which view (month) is selected. I am able to change the view by
month but the cell range in the totals column does not change with custom
view feature. Is there anyway to do this at one time without having to
create a new file for each month?
Thank you.

 

[Don Guillett]

Here is one I just did for a client that should give you the idea. This one
hides all days that are not the day of the date in the target cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub
Application.ScreenUpdating = False
Set myrng = Range("c5:bd5")
[myrng].EntireColumn.Hidden = False
For Each c In [myrng]
If c <> Format([b4], "Ddd") then c.EntireColumn.Hidden = True
Next c
Application.ScreenUpdating = True
End Sub

Sub UnhideAllColumns()
Columns.EntireColumn.Hidden = False
End Sub

 

[Debra Dalgleish]

If you have the month names in the headings, you could use SUMIF,
instead of SUM. Allow the user to select a month from a dropdown list.
That could run a macro to change the view, and the month could be used
in the SUMIF formula.

There are sample formulas here:

http://www.contextures.com/xlFunctions01.html#SumIf

 

[Guest]

Thank you for the hepl Don

Here is one I just did for a client that should give you the idea. This one
hides all days that are not the day of the date in the target cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$4" Then Exit Sub
Application.ScreenUpdating = False
Set myrng = Range("c5:bd5")
[myrng].EntireColumn.Hidden = False
For Each c In [myrng]
If c <> Format([b4], "Ddd") then c.EntireColumn.Hidden = True
Next c
Application.ScreenUpdating = True
End Sub

Sub UnhideAllColumns()
Columns.EntireColumn.Hidden = False
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

I am looking for a way to change the view i.e. change hidden columns
representing months and change the cell range sum within a totals column
depending on which view (month) is selected. I am able to change the view by
month but the cell range in the totals column does not change with custom
view feature. Is there anyway to do this at one time without having to
create a new file for each month?
Thank you.

 

[Guest]

Thanks for the help Debra

If you have the month names in the headings, you could use SUMIF,
instead of SUM. Allow the user to select a month from a dropdown list.
That could run a macro to change the view, and the month could be used
in the SUMIF formula.

There are sample formulas here:

http://www.contextures.com/xlFunctions01.html#SumIf