MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Date range in pivot table

  Asked By: Richard    Date: Aug 14    Category: MS Office    Views: 1325

I will try again and
rephrase the question. I have an excel database that includes names,
project numbers, week ending dates, and hours. The database contains
information covering several months, and will continue to get larger.
I have a pivot table that shows all this data. Now I need to set up a
user form that will allow me to enter specific "from" and "to" dates,
and then modify the pivot table to show only the names, project
numbers, and hours matching this criteria.

Does anyone have an idea how I may accomplish this? Any assistance
would be most greatly appreciated. If I have somehow done this
incorrectly, or perhaps have not been patient enough regarding my
previous posting, my apologies.



4 Answers Found

Answer #1    Answered By: Beaudi Smith     Answered On: Aug 14

This is a multi-part question. The first thing to note is your use of
Excel as a database. There was a time I would argue that Excel is a
financial utility and Access is a database  system, however, with Office
2007 about to hit shelves with it's 1Million + rows and 16,000+ columns
per sheet, I believe Excel will become more of a database tool whether
experts want it to or not.

On to your questions.

1) form  and dates

a. Insert a User Form from within the VBE. Add two text fields for
date entry, and if you want, you can even include a calendar utility
from the tools part of the forms toolbar. A little more code will be
needed to grab the date  from the user selection, but it makes the
program look slicker.

b. Have the users enter  the "to" and "from" dates  into the
appropriate fields, and then run your program based on these dates by
adding a button to the form.

2) The pivot  Table

a. There is more than one way to approach this, but one is to take
your data  from step 1, which will filter only the records you want, and
have this data copy to a second spreadsheet. Have your pivot table  work
off this second sheet, and viola, you have only the records showing from
the chosen date criteria.

Answer #2    Answered By: Sophia Campbell     Answered On: Aug 14

I have modified my User
Form to include list boxes for the "From" and "To" dates, which coincide
to the same date  range that my input form  uses. I also put a check mark
command box in the User Form if someone just wants the pivot  table to
use the whole database.

Here's the part I'm still not tracking: How do I get my Pivot table  to
extract only the data  from the date range  I specify?

Answer #3    Answered By: Andrew Brown     Answered On: Aug 14

I use Access as my database, and what I do is loop through my recordset,
store my data  into an array, and then place this data into an existing
spreadsheet. I do this from within Excel. Once the data is loaded I
refresh my pivot  table, which is also done via VBA and my pivot table  is
up to date. Since you are using Excel as your database, you may want to
contemplate looping through your date  range to get the rows the user
wants, then either store in an array or copy the selection to another
worksheet. Use the new worksheet as the base for your pivot table, and
refresh. Be sure to change the pivot table settings to not save data
with table layout so all the updated data is recognized with the
refresh! Note that once you create the new worksheet, and link your
pivot table to it, you only need bring in new data to the sheet and
refresh the pivot table afterwards.

Your other option is to work with your existing pivot table, and to
write a loop statement to work through the "Date" field in the pivot
table. You will want to identify each item date value the user doesn't
want to be seen as false, or clear the selection, and identify each item
date value the user selects as true. You'll have to play with this to
decide what is best for your situation, and design.

Answer #4    Answered By: Gustavo Costa     Answered On: Aug 14

I'm afraid however, that I may have jumped in way over my
head in putting my question  to this user group. I'm afraid that I have
little experience in Microsoft Access, and when you use terms like
recordset, array, and looping, you go totally out of my league.

I will attempt to find another solution to my problem. Sorry to have
bothered you.

Didn't find what you were looking for? Find more on Date range in pivot table Or get search suggestion and latest updates.