MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Showing pivot table dates


Posted by Brad on December 20, 2000 5:38 AM

I have a pivot table based off a large database. The pivot table is sorted by supplier, part number, then due date. I only want to show due dates that are within the next 35 days. How do I do that?

Brad


Posted by Bruce on December 20, 2000 7:06 AM


One way is to curser in the date field of the pivot table, right click, select group and ouline,
and then group, and write in the "staring at" and "ending at" dates.

Posted by Brad on December 20, 2000 10:00 AM


Bruce,
the database is too large and when i tried that it told me there were too many cells to group and then proceeded to lockup my Excel. Is there another way?

Brad

Posted by Bruce on December 20, 2000 10:32 AM

Sorry Brad,
Try this: Select Data, Filter, Auto filter.
Then hit the drop down arrow in the column, select custom, and enter the criteria.


Posted by Brad on December 20, 2000 11:25 AM


Bruce, you can't filter in a Pivot Table and I don't have access the database, just the pivot table. the database is a separate .csv file.

Posted by Bruce on December 20, 2000 11:54 AM

Brad,
Maybe someone will have a better idea, but the only thing I know to do is to select the pivot table, copy, edit, paste special, values, into a new sheet, and then use the filter,


Posted by Thomas Venn on December 20, 2000 3:59 PM

Hi,

Maybe this will work. Assumes your date is in column a1. Assumes your data does not begin until cell a3.

Cheers,

thomas

Sub Macro1()
Application.Goto Reference:="R1C1"
Application.Goto Reference:="R1C27"
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(NOW()-RC[-26]<35,RC[-26],"""")"
Selection.Copy
ActiveCell.Range("A1:A10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Application.Goto Reference:="R1C28"
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",""hide"")"
Selection.Copy
ActiveCell.Range("A1:A10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Calculate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
:=".", FieldInfo:=Array(1, 1)
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Find(What:="hide", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"
End Sub
Sub Macro2()
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Find(What:="hide", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R1C28"

Application.Run "Macro2"
Application.Run "Macro2"
Application.Run "Macro2"
Application.Run "Macro2"
Application.Run "Macro2"
End Sub


Posted by thomas venn on December 20, 2000 4:01 PM

oops, i meant column A.

and... you have to run macro1 first, then run macro2 after macro1 finishes running.

cheers,

thomas

Posted by Tony Scala on December 21, 2000 5:27 AM


You can use a filter within the pivot table by highlighting the first blank cell to the right of your pivot table then dragging left over your pivot table. Then hit data, autofilter, and you will get your filter boxes. This will allow you to filter your pivot table. TS

Posted by Bruce on December 21, 2000 7:07 AM

Tony - I didn't know Excel could do that. Thanks for the great tip!