B
Brian
I want to use a scroll bar to control which 18 months of data are viewable.
Right now I have an ActiveX scroll bar linked to a cell with the minimum
being 11 since the earliest data I want is 11/1/07. In the cell below that I
have the formula =DATE(2007,AB3,1), which gives me the starting reference
date. Back in Column C I put the formula =$AB$4 in cell C7 then below that
(and each subsequent row) =DATE(YEAR(C7),MONTH(C7)+1,1) for 18 months. Then
the 4 columns to the right of the date has the various information for that
month populated through a pivot table on another sheet using =GETPIVOTDATA
method.
This works however it is very slow, 5 seconds or so. I did some trouble
shooting and my scroll bar updates it's linked cell almost immediately. I
also get my reference date quick. The lag seems to be cell C7 getting the
date from AB4, this part is taking pretty long, considering it's a pretty
simple formula. Everything that is supposed to take place after it gets the
date happens pretty quick.
Is there a faster method in doing this, or why is this taking so long to
complete that simple calculation.
Thanks in advance.
Right now I have an ActiveX scroll bar linked to a cell with the minimum
being 11 since the earliest data I want is 11/1/07. In the cell below that I
have the formula =DATE(2007,AB3,1), which gives me the starting reference
date. Back in Column C I put the formula =$AB$4 in cell C7 then below that
(and each subsequent row) =DATE(YEAR(C7),MONTH(C7)+1,1) for 18 months. Then
the 4 columns to the right of the date has the various information for that
month populated through a pivot table on another sheet using =GETPIVOTDATA
method.
This works however it is very slow, 5 seconds or so. I did some trouble
shooting and my scroll bar updates it's linked cell almost immediately. I
also get my reference date quick. The lag seems to be cell C7 getting the
date from AB4, this part is taking pretty long, considering it's a pretty
simple formula. Everything that is supposed to take place after it gets the
date happens pretty quick.
Is there a faster method in doing this, or why is this taking so long to
complete that simple calculation.
Thanks in advance.