Posts by epynephrin

  • 2-dimensional Date Range With Dynamic Copy And Paste

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


    Hm. That is food for thought.


    I'm thinking I'll just stick it out with what I've come up with. I recall there being a reason--either a design request, or my personal preference--behind avoiding the style you've suggested, Jim. I'm looking and thinking to myself "197 line items for 20 participants? Seems a bit much."


    Again, many thanks for all your help. The way my system is coming together, that method wouldn't be effective or clean enough. Thanks for trying, though. I will keep it in mind on future projects.


    Thanks to everyone who tried. I think that my solution a few posts ago, the refresh macro, best solves my needs. This thread can now be laid to rest.

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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?

  • 2-dimensional Date Range With Dynamic Copy And Paste

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



    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.

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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...

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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.

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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.

  • User Form To Add Data In Columns

    Re: User Form To Add Parts Into Columns


    I've always been a fan of the short, sweet "Do Until you find an empty cell" loop:

    Code
    Do
        
                            If IsEmpty(ActiveCell) = False Then
                        
                                ActiveCell.Offset(1, 0).Select
                        
                            End If
                        
                        Loop Until IsEmpty(ActiveCell) = True


    I've used it numerous times in various projects to find empty rows and columns. To make columns, though, you have to change the middle line, the Activecell.Offset to read

    Code
    ActiveCell.Offset(0, 1).Select


    I do have to agree with the point Norie raises. Is there a reason you're organizing it by rows instead of columns? I find that data like this flows a little better (and is easier to read/code/think about) if you're looking at it as Row line items.

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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?

  • 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.



    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?

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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.

  • Vlookup Formula Multiple Results

    Re: Vlookup Formula Multiple Results


    Quote from carlynishere

    Ahh...I see that now. That is the problem. I can fix that!! Thanks!


    I was noticing that with the SMS and Telephony categories as well. I came up with a slightly different formula that also worked for all of your rows except for one.


    =VLOOKUP(A11,'FT-BGAN-RATES'!$B$2:$J$12,INDEX('FT-Call-Detail'!$N$15:$O$21,MATCH('FT-Call-Detail'!G11,$N$15:$N$21,0),2))


    The problem cell was K43, because "BGAN IP STREAMIN" wasn't in the little helper box you added.


    A tip that might help you with that part: you can make the cells in column G have a drop-down menu of all the values in the helper box (N15:N21).

    • Select the cells in Column G that you want to add the list menu to.
    • Go to the Tools menu and click Validation.
    • When the Validation wizard appears, set the Allow: field to List.
    • Then select the cells N:15:N21 for the Source field.
    • Press OK.


    What this does is make excel fill in your value of choice from the list in those helper cells. That might also make data entry a little easier.


    If it helps, here's an example:

  • Vlookup Formula Multiple Results

    Re: Vlookup Formula Multiple Results


    What is the value that, say, cell K11 is supposed to have in it? If there's any logic that might need explaining, that'll probably help, too.


    I feel like I recently did something similar, so I might have some insights (but we'll see).

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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!

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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.

  • Hide Sheets Based On Data In Cells

    Re: Hide Sheets Based On Data In Cells


    Quote from csj1

    You can do this with check boxes with one being yes and the other being no.



    Chris


    Does that code work on Excel sheets as well as Userforms? If so, how does one add buttons to the sheets?


    (Just asking because I don't know offhand, and I'm probably not the only one.)

  • Now() As Record Id

    Re: Now() As Record Id


    I had a similar piece in a project of mine. I keep looking at your code and getting a little lost (which might be because I'm in the same boat as you, learning VBA as I make mistakes with it).


    Do you need the timestamp to go into a cell at the end of the record you've added? If so, the code can be much simpler:


    Code
    Cells(y, x) = Now


    (where Cells(y, x) is the cell you want the Timestamp to land. It could also be ActiveCell or an ActiveCell.Offset(y, x))


    Does that fit your needs? It worked for me, so I'm tossing it out there.

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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!)

  • 2-dimensional Date Range With Dynamic Copy And Paste

    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.