2-dimensional Date Range With Dynamic Copy And Paste

Hello, Experts

Some of you may have seen my Previous, related thread thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note.

The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating.

I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent.

What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question.

The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the Previous, related thread thread. The new problem comes when a user tries to generate a report from this template. When a report is generated, the Template gets copied and pasted up to 5 times (in a yearly report, once for the total year, and once each for quarters 1-4), so I need a way to keep the date ranges dynamic to the iteration of the report.

What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.

So, my main question, I suppose is: Is there an easier way to do this?

I am more than happy to re-explain myself if I've confused anyone. I'm so close to this project (and half-crazy already), that it makes perfect sense to me all the time--other people, maybe not.

Any help is greatly appreciated.
--epynephrin

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

Quote from epynephrin

What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question.

The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the Previous, related thread thread. The new problem comes when a user tries to generate a report from this template. When a report is generated, the Template gets copied and pasted up to 5 times (in a yearly report, once for the total year, and once each for quarters 1-4), so I need a way to keep the date ranges dynamic to the iteration of the report.

What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.

So, my main question, I suppose is: Is there an easier way to do this?

Display More

If it helps, this is the actual question and the relevant information. The rest is me explaining why I'm double-posting, expressing gratitude for help so far, and giving the motivation behind the question.

I would attach a small workbook example, but I'm not sure I can cut it down from the 320k file I've been working with--at least, not in such a way that the interrelated formulas, macros, userforms and codes will be preserved. I will try, but I'm skeptical I can trim it down to the 45k limit.

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

Okay. Here's the extremely light version of what I have. The only way to get it to the size limit was to delete most of the data (there are 5 records, three classes, and two departments down from the usual 18, 19, and 9 respectively), all of the userforms except for the one which generates the report, the Workings sheet (Which is supposed to be set as xlVeryHidden, all of the relevant data has been moved to the Participant Master List). So, I'm not sure this is an accurate representation of what's there, but at least it's something.

To see best what I need, open the Macros menu and run the only Macro there, which brings up the userform. Select the 2007 Yearly Report option, and you'll see what the report should look like. The thing I'm trying to wrap my head around is the Site/Department box at the bottom, and making it update itself correctly when it's pasted across the report five times.

Bump

Bump

Haven't seen a reply yet; although I know it's a tough problem. I'm hoping that by bumping my thread back up, someone else might have better luck.

Think I forgot to note that the number of classes mentioned in the above reply, and the number of participants/students is able to (and likely will) increase over time--the latter will increase greatly; my draft currently has 18 fake names (borrowed from TV shows, films and books), only five of which have been preserved for the light version.

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

Thanks! Here's hoping.

Simply put, I need the formula from the Previous post, http://www.ozgrid.com/forum/showthread.php?t=69141.

Then, I need it to work across multiple sheets (not too hard), and be flexible enough to allow for new classes and participants, as described above, without having to re-write the formula each time a participant or course offering is added--as the end user is not an Excel expert. (much harder)

Then, I need it to be dynamic enough that someone could use the formula multiple times for multiple date ranges. Up to five times per report, potentially on multiple reports. (next to impossible, by my expertise).

Can anyone make that happen? When you're done with that, I want the Sistene Chapel re-done on my apartment ceiling.

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

Quote from JimFuller1

epynephrin,

Looked at the previous post though I missed it when it happened. I was struck first off by the fact that nobody suggested using a Pivot Table to generate the reports so they don't actually contain any formulas. Could you set up two sheets each with a Pivot Table that references the database of course participants, and use VBA to copy the results of the queries to one report page? I mean, you've got a Cringe formula and a lot of time invested, but I think they are a substitute for what a native Pivot Table will do.

In my world, I would even insist that the users learn how to minipulate the Pivot Table (in read only mode) to create the results they want. That would accomplish two things. More Excel knowledge in the office (never a bad thing), and the user may discover different questions to ask that your report doesn't cover.... yet.

Jim

PS-I hope Foreman quits don't you?

Display More

Pivot Tables... Hm. I'd considered them early on, but found myself shying away because I wasn't able to manipulate it to give me the right data. It's one of those things that I'm afraid of because I don't totally comprehend them--I can make them, but getting them to work is another matter entirely ("work" being defined as "doing what I want them to do"). I'll give it a try and get back to you if I get completely stumped (likely).

Thanks!

p.s. I'm not sure I want Foreman to leave. I find he brings a balance to the show, and a certain literary je ne sais quoi. I'm also an English major, so I look for those things. I also can't really see them introducing another character to take his place very successfully. On the network branding side, I'm skeptical he will, though, because he is the "Token Minority" (I say, hoping not to offend anyone).

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

On second thought, that's why I decided against pivot tables: I'll be adding to the "Class Offerings", which in the sample workbook are the columns starting after Site. "FOCUS" is the first one, and I'll need to add in later classes, and I couldn't do that automatically with a Pivot.

On the plus side, I think that some of my end users have seen Pivot Tables before. I got the idea for them from another report some have used.

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

I'm afraid not. Maybe I missed that part of the Pivot Table tutorial (MS Online help). Where would one tell the Pivot to use a dynamic range?

And, can I add participants in? I'm assuming so, because my Add Participant userform inserts a row above the first empty one for new participants--but this won't screw up the Pivot Table setup?

Once I've gotten all that down, can I use the cells in the pivot exactly as I would any other cell in Excel (for VBA codes)?

Could you perhaps enlighten me a little more--assuming you have the time and patience?

I'm showing off my limited understanding of Pivot Tables now! =P
(but if you need any literature analyzed, I'm your man!)

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

Hey everyone,

Junho, I'm afraid your solution hasn't helped much. I'm looking for a way to report based on a date range. Your formulas look like they're reporting based on anyone being in the system. While this is a good function, it's not what I'm looking for. Sorry

Krishna, I hate to tell you, but I think you've done a slightly more sophisticated version of the formula Cringe2 came up with on the previous post. Sorry, that doesn't help me too much. Unless I'm misunderstanding the (tCount>0) statement...

Sorry, guys.
--epynephrin

The biggest question I still have is: How can I make the Site/Dept box copy and paste five times, and still tell me how many people from a site and department attended anything within five different, specified date ranges (one range per each Copy/Paste iteration)?
Ideally, I'd like it so that if a user changes the date on the report (the copied/pasted version), the numbers would also change accordingly.

At the moment, my plan of attack is to make a macro which will update the dates in my helper column first, then calculate the formula into a "holder" version of the Site/Dept box, and finally assign the values from the holder into the final report version.
...the problem is that this is neither as dynamic as I'd hoped, nor am I entirely able to wrap my head around it yet.

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

Quote from junho lee

instead
=SUMPRODUCT(--(Sites=$A12),--(Dept=C11),--(tCount>0))
replace
=IF(VLOOKUP(A12,A4:C8,3)=0,0,SUMPRODUCT(--(Sites=$A12),--(Dept=C11),--(tCount>0)))
it will works?
regards,

I'm not sure I follow you, and I'm afraid I still don't see the date range in here.

If I'm guessing correctly, that formula should go into Krishna's spreadsheet, in the Site/Dept box we're working on. But, I'm not sure I see what you're going for.

Sorry. Thanks, though!

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

If it helps you to see what I'm getting at, I suggest you take a look at the Reports userform. There's a Macro to summon it, should be the only Macro on the workbook. Perhaps that will lend more clarity to what I'm aiming for.

On the userform, there are four types of reports: Date Range, Monthly, Quarterly and Yearly. These reports copy the Template, and alter the dates in the Date fields on the template.

The monthly and date range reports use the template once. The Quarterly report uses it four times: once for the entire quarter, and once each for the included months. The Yearly report uses it five times: once for the year, and once each for the quarters.

I use the template as a test mode, as the report generating mechanism hasn't given me any trouble so far. If the dates are in the right place, I've found the report to be fine.