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:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$5" Then
    Select Case Target.Value
        Case "X", "x"
            Range("C5").Value = Time
        Case "Y", "y"
            Range("C6").Value = Time
        Case "Z", "z"
            Range("C7").Value = Time
    End Select
End If
End Sub

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: Create List Depending On Lookup Value

Quote

Why can't you use a pivot table to create the list?

HTH

Re: Hide Formula Result When No Data In Range

um - this formula:

Quote

IF('4. NORMALITY ASSESSMENT'!N55>'4. NORMALITY ASSESSMENT'!C5:C10,"YES","NO"))

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

HTH

Edit:
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: Hide Result When No Data In Range

would
=if(counta(C4:C54)=0,"",your formula)
not do it?

Re: Count Used Cells In Range

bt Dve, I tht u wz cool. innit.

many apologies for the dirt above, I can't resist though

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.

HTH

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 in principle the solution will be an if looking above and to the left.

This is basically clairvoyancey, which is not a word.

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: Nested If

You're gonna get banned with a thread title like that.

More to the point, we're not really here for homework assignments.

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

I have to admire your tenacity. You should be in business, not research.

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

Quote

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

Display More

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

Third...

Quote

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.

HTH

Re: Fill UserForm ComboBox With Values In Range

We could be helping Kempy too - it's hard to tell because they're banned too.

Helloooooo out there. Is anybody theeerrrreeeee???

Re: Static Date & Time Corresponding To Cell Change

Further to Dave's code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target(1, 4) = Date
        Target(1, 5) = Now
        Columns("B:C").AutoFit
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
    If Target.Column = 6 Then
        Target = Date
        Target(1, 2) = Now
        Target(1, 3) = "=RC[-2]-RC[-4]"
        Target(1, 4) = "=RC[-2]-RC[-4]"
    End If
Application.EnableEvents = True
End Sub

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)

HTH