Posts by Cheeky Charlie

    Re: Convert NOW() To Static Date & Time

    If you're going to use a macro, you might as well use it to do the "work" too - then you don't have to worry about losing formulae:

    Put in worksheet

    You could rewrite these lines:

    Range("C5").Value = Time 'old
                if Range("C5") = "" then Range("C5").Value = Time 'new

    and similar, this would make it only write the time in the respective boxes if they were empty

    HTH[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Also, putting

    ' Keyboard Shortcut: Ctrl+t

    doesn't tie a macro to a keyboard shortcut. This forum has an interesting thread on where that information is stored (started by someone who had tried writing all sorts of interesting comments about keyboard shortcuts, none of which made a keyboard shortcut...

    Re: Static Date & Time Corresponding To Cell Change

    yeah, delete the line

    Coding is generallyvery logical - it's not hard to understand because it's crazy or irrational, like a woman, it's hard to understand because you need to learn a language in order to use it - like... um, well, something else.

    Re: Hide Formula Result When No Data In Range

    um - this formula:



    Doesn't make any sense - it will only check the first value of the range C5:C10 against N55 - i.e. C5. If C5 returns an error value, your formula will return that error value.

    In summary, check the ranges of your formula.

    Re: Calculate Times Greater Than 24 Hours

    Format your cell: "[h]:mm:ss" (as opposed to "hh:mm:ss") to show more than 24 hours in hours


    Kris, what is the point of your formula? As far as I can tell it does the same as simply =A1-B1 but gives some slightly different results in the realm of negative time. I ask because I've seen your posts and know you're a-freakin-mazing - not because I think you've done something wrong!

    Re: Return Row Count

    Quote from Sicarii

    I don't know how Yard and Cheeky even understood what you wanted. If they didn't either; maybe a sample file would help...

    It's a distinct possibility :wink:

    I have a lot of time for trying to unpick garbled queries, to me it says "I really don't get this" more than it says "I'm too lazy to write this in intelligible English". I know I've had my hide ripped off for not understanding things I "should have" and it doesn't feel good...

    Re: List By Month

    Mmm, inclined to agree with Dave, you can use my solution to present the data in exactly the format you've asked for, but it would be easy to run your mailmerge from your raw data. i.e. two tools, one for each job. TO me it seems the jobs are similar, but distinct, so it seems reasonable to do it that way.

    Re: List By Month

    How do you like these apples?

    See comments in worksheet.

    Instead of defining just by month you can put any start and end date in the indicated cells.

    My pivot (as always) uses a dynamic named range so this sheet is updateable with new lines.


    PS my solution delivers a summary on another page, as requested, but it would be easy to change the location of the pivot to the same sheet.

    PPS shoddy thread title

    Re: Find On Off Time And Report On One Sheet

    I think I can help with a formula but I'd need your file to be backwards compatible.
    If you want me to help, would you mind uploading a trimmed (so you can upload it) and 2003-compatible version of your sheet?

    PS You'll generally get more help if it's easy to get hold of the files you want help with.

    PPS we called it current when I was at school

    Re: Return Row Count

    =counta(Sheetx!A:A) would work for one column

    If you have gaps in your data (fix it, put zeroes (which can be hidden from view) or "null") or you could add a concatenation column to one end of your data.
    i.e. cell A1 =roundup(counta(A1:Z1)*0.00001,0)
    other cell = sum(Sheetx!A:A)

    *this assumes you've added a helper column (interesting name...) as Yard alluded to.

    Re: Conditionally Delete Rows In Spreadsheet

    Try putting autofilter on:
    Data-> Filter-> AutoFilter
    Best to select your "headers" row before you do this so Excel know which line to apply your filters to.
    Now click on the little arrow by phone 1, scroll down to "Blank cells" and click that.
    Now click on the little arrow by phone 2, scroll down to "Blank cells" and click that.

    Now you can see all the entries without either phone number.

    You can delete these rows perfectly safely - when you "unfilter" the columns you will be left with the data you want.

    This is not nearly as fancy as a code solution, but actually, it's easily the most appropriate way of achieving what you've asked.

    HTH[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]On re-reading, I'm not sure whether you mean:
    delete if B and C empty
    delete if B or C empty.

    I'm sure by stepping through what I've shown, and seeing how advanced filter makes it quick & easy to find info you want, you will be able to furkle with your data in a whole excitement of ways.

    Re: Highlight Formula Reference On Another Sheet

    IMHO the best you can do would be the Goto precedents/dependents which Hatman has alluded too (press Ctrl + G whilst on the cell in question and pick your poison).

    What you're asking for is more about the design of Excel itself than how to use it - we can't really fix that here!

    Perhaps some git with 2007 will wade in to tell you how fantastic that is for exactly what you need(?)

    Re: Static Date & Time Corresponding To Cell Change

    Crikey, you've a lot to say...
    First, thanks for your response, it makes all the difference when people are grateful.
    Second, If you read my post, I do tell you how to format the columns in your worksheet so it looks how you asked (I am very thorough...)

    If this doesn't make sense, please do ask the so called experts :wink: in your office - this is quite basic.



    and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.

    It's what you asked for!!!

    Anyway, a quick solution would be to insert a couple of lines like this:

    If Target.Column = 6 Then
        if target.value = "end" then 'this is an added line
            Target = Date 
            Target(1, 2) = Now 
            Target(1, 3) = "=RC[-2]-RC[-4]" 
            Target(1, 4) = "=RC[-2]-RC[-4]"
        end if  'so is this
        End If

    This would make your code trigger if someone types "end" (without quotes) in the end date column.

    Fourth: Your last question should absolutely be another post - remember how this forum is designed to help people to find solutions to their problems. I would also suggest you search on this topic heavily before you ask a question - as far as I can tell it has been covered quite thoroughly already.


    Re: Static Date & Time Corresponding To Cell Change

    Further to Dave's code:

    You will also need to format your columns:
    D & F as date
    E & G as time
    H as a number
    I as "[h]:mm" (or "[h]:mm:ss")

    This will work when anybody selects a cell in the end date column (F)