2-dimensional Date Range With Dynamic Copy And Paste

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Hm. I have a working version of what I was talking about earlier. I was hoping to make it more dynamic--such that the system would update the cells I've been rambling on about for the past week whenever the date range changes. Instead, I have the following Macro which is called when the system is told to make a report.

Sub SiteDeptRefresh()


    Application.ScreenUpdating = False
    
    TempVal = 0
    
    Workings.Visible = xlSheetVisible
    MQYreport_TEMPLATE.Select
    MaxDept = Cells(2, 18)
    MaxSite = Cells(2, 17)
        For Site = 0 To MaxSite
            For Dept = 0 To MaxDept
                Workings.Select
                Range("Workings_DeptSiteTotals").Select
                TempVal = ActiveCell.Offset(Site, Dept).Value
                MQYreport_TEMPLATE.Select
                Range("MQY_DeptSiteTotals").Select
                ActiveCell.Offset(Site, Dept).Value = TempVal
            Next Dept
        Next Site


End Sub

It works, but it's not exactly what I was looking for. Perhaps this, and all of the above posts/replies/samples/examples/information will help?

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Wow. This thread has gone on forever. It just refuses to die!

Jim,
I think the problem I've been having with the pivot tables is where to put what where. There are the four "areas": Data, Row, Column and Page. My assumption was Row/Header would be Site or Department, and Data would be each of the 19 classes. What I found was that as I added each class in, the table became more and more unwieldly.

So, perhaps I'm adding the wrong pieces to the wrong place.

Junho,
I like where you're going! I hadn't even considered that, and it looks like it's a step in the right direction. There is one slight problem, though. Try the following steps, and see if what I did comes up on yours:

• Go to Sheet 1, clear Cells F2, E3, and F5. (Change the data slightly)
• Go to the 2007 Yearly Report page, and change cells D21 to 1-Jan-06 and G21 to 31-Dec-06 (make a 2006 date range)
• Change cells D40 to 1-Jan-03 and G40 to 31-Dec-03 (Make a 2003 date range for comparison)

What I'm seeing is that on the Report page, in the 2003 and 2006 date ranges, the Site and Department breakdown has a 2 the Lethbridge/Individual Ops boxes (cells C51 and C32), but because we changed the data in "step 1", that result should only be 1.
That is, in the year 2003 (or 2006), only one person attended a class in Lethbridge's Individual Ops group.
Do you have an brilliant ideas on how that can be corrected?

Re: 2-dimensional Date Range With Dynamic Copy And Paste

I'm afraid that still didn't work. I think the problem is that your formula:

=SUMPRODUCT((Department=D$11)*(Site=$A13)*(First_Name>0)*($C5>0))

is returning all results anyone working in the site and department in question, because C5 is greater than 0. Because there are two people working in Lethbridge's Individual Ops group, and the C5 result is 1, it looks like your formula is counting both people. I'm trying to get it to only count the one person who took the class.

Re: 2-dimensional Date Range With Dynamic Copy And Paste

I'm still a little confused by that (Although, adding the color has made it a lot clearer; there is color on mine, but I cleared it out to try and make it fit the size limit). The problem is that it looks like the formulas you've got there are really manual and labor intensive to maintain--unless I'm just misinterpreting them. (If anyone else is reading this, am I the only one who's getting lost?)

I hate to say it, but I'm starting to think that the best way to do this is with Cringe's formula and the copy Macro that I've included at the top of page 3.

I appreciate your help, everyone.

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Quote from JimFuller1

Unless you want to use the Pivot Table.....

Good luck,
Jim

I tried. I still haven't figured it out. I assume that I need to put Site under Rows and Dept under Columns (or vice-versa). I then added each of the 17 classes in as the Data fields. What I came up with was massive and didn't really answer my question.

By each site, I have 17 rows which each counts up a class that has been taken. I couldn't see a way add a date range to them, and I couldn't really see my data, as it fell off the screen on both sides.

I then tried just putting in the Number of Courses Completed for Data, instead of the actual courses. It was a little more sane, but still didn't answer the question of how many people went to a class between Date X and Date Y. I'm afraid I haven't seen that in the pivots.

Sorry, am I missing something?

Thanks,
--epy

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Quote from JimFuller1

epynephrin,

I guess I won't be able to say how to do it unless I see the data. Can you post some useable data that we can both work with?

Jim

Certainly!

What you have there is my fake data (now with more fictional characters per serving), and the two attempts I was trying to explain. I know it looks a little weird the way it's organized, but I assure you there's some method to my madness (although mostly madness)--as I mentioned at the beginning, the actual final product is far too big for me to upload here.

The question I'm trying to answer is: How many people from [site] [department] took any classes between [Start date] and [end date]. The start and end dates are on the Report Template I posted at the beginning of this mammoth thread, I assume that--if I can make the PivotTables do what I want them to do, they'll wind up on the hidden Workings sheet I referenced somewhere else in this thread.

Thanks. Your help is greatly appreciated.
--epy

p.s. I have no idea what a Sun Code is...

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Quote from JimFuller1

epynephrin,

Soapbox Warning!!!
I sure you thought that layout for the data made sense but, what you have done is complicate your life beyond what is necessary. You're right, there is no way to have a Pivot Table do what you want BECAUSE of the way the data is layed out. I'm sure the whole reason you're having these long posts is because of trying to work around data that is layed out wrong.

If you just used common database layout, built in functionality like Pivot Tables would do most of what you want without effort.
Stepping down from Soapbox...

Sorry I couldn't help.

Jim

Display More

Maybe I've just never seen an actual Excel database layout, but based on the few I've seen I'm feeling like I've missed something. Is there a better way for me to track this whole thing, then, and I've just been too stupid the whole way along?

I would be willing to take a look, if you were to have the time and patience to suggest it, at a better way to handle this. At the moment, I have a system that seems to work; I'm not sure it's perfect. Considering I knew nothing about VBA and a bit about Excel when I got into the project, I'm sure there's always a way to make it simpler--hopefully a little more idiot-proof than usual.

Here's hoping I don't feel ridiculously dumb if/when I find out that there's a free Excel thing that does everything I've manually programmed for me.

If you're thinking Access, the problem is that I don't have it available to me, which is mainly why I'm reinventing the wheel.

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Hey, Jim

Thanks.

No worries, I am fairly prone to self-effacing humor (and the internet isn't prone to sarcasm). I figure it is a lot better to adapt my Wheel 2.0 instead of scrapping it when I learn there's an original, too.

So, where's the attachment?