View and Cell Sum Range
 

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.
--
Dewayne

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

"Dewayne" wrote in message
...
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.
--
Dewayne

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 he

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

Dewayne wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you for the hepl Don

"Don Guillett" wrote:

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

"Dewayne" wrote in message
...
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.
--
Dewayne

Thanks for the help Debra

"Debra Dalgleish" wrote:

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 he

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

Dewayne wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html