Posts by JMAN

Thanks again, Rory. Once again, you came through!

Hello!

I need some help with a formula that will calculate sick time earned starting on the employee's anniversary date (cell J11). The employee will accrue 4 hours every 30 days, but will not earn more than 48 hours in 12 consecutive months. I'm afraid this is beyond my ability so any help is much appreciated. I've attached the workbook.

Both solutions work - Thank you!

Hello, it's been a while!

First, Sorry if my title isn't good, but I really can't figure out how to summarize what I need for a proper title.

I have a simple workbook that keeps track of odometer readings. I'd like to have a column that shows the Year To Date Odometer reading based on the data entered every quarter. The problem is some cells will be blank because we haven't go to that quarter yet so it is not giving me proper results. The formula that was tried is "=F8-E8+G8-F8+H8-G8+I8-H8". This formula works if all the cells have data, but if there blanks it doesn't work. I've attached a sample for review.

Does anyone have any suggestions? My brain just isn't cooperating today. Thanks in advance.

Re: Only allow "Y" or "N" entries in cells without blanks

HA! I have nothing....That was so simple. I told you I was rusty! Thanks for the info.

Hello everyone, long time no post!

I'm trying to use Data Validation for cells. In these cells I only want a Y or N entry and cannot have blanks. I'm rusty on formulas and could use a little help if possible.

This is what I've come up with, please let me know what you think.

=IF(NOT(ISBLANK(B6))*OR(B6="Y",B6="N"),"TRUE","FALSE")

Re: Compare previous years earnings to current year by month

That worked great, thanks!

Hello all,

I'm a bit stumped on finding the correct formula to compare data. Here is what I'm trying to accomplish:

1. There are two sheets, "2011" and "2012"
2. Both sheets have all my offices listed with their earnings for each month
3. I'd like a formula in the 2012 sheet that will compare the year to date earnings for each office to the 2011 sheet, by month. For instance, we are in August right now so my 2012 sheet has earnings listed for January through July. I'd like a running total that will tell me what the year to date earnings were for each office through July in 2011. So if this year my Little Rock office has 500 dollars of earnings through July, I'd like to know what that number was in 2011.

I've attached a sample that will explain it a little more clear. Any help is appreciated as I'm a bit rusty with formulas.

JMAN

Re: Defining the same variable, repetitively

Yes, makes perfect sense. Thanks for the input, I appreciate it.

JMAN

I have a variable named public variable named MonthEnd. This variable will always return the month end date as "MMYY". I use this to help name extracted reports for the month.

I use this several times throughout the workbook so I was wondering if there was a way I could define this variable once, instead of in every routine? For instance, every time I use it, it is always:

MonthEnd = Format(Sheet1.Range("C3"), "MMYY")

.

Is there a more efficient way to define this variable once or is the way I'm doing it now the most efficient?

Any help is appreciated.

Re: Quitting Excel Application through ribbon control

Quote from rory;546969

Change the callback to:

Code

Sub ActivateOUCompletion(control as iRibbonControl)
Application.Ontime Now(), "FinishOU"
End sub

and see if that works.

Worked like charm! Thanks a lot for that. I was racking my brain trying to figure this out. I'll have to do some homework on application.ontime; I've never used it. Thanks again, Rory.

Re: Quitting Excel Application through ribbon control

Quote from rory;546966

Presumably that code is being invoked by your callback? Does it help if you have your callback invoke that code using Application.Ontime rather than calling it directly?

I'm not sure I follow you on the Application.Ontime suggestion. Yes, this code is invoked by the callback. I just edited the code. Notice it is now

Sub FinishOU (control as iRibbonControl)

I even tried changing the sub just FinishOU() and then create another sub named:

Sub ActivateOUCompletion (control as iRibbonControl)
Call FinishOU
End sub

this had the same result.

Re: If statement to select cells only if not blank

What about sorting the data before copying so that the blank cells are the bottom. Then copy/paste?

I some simple code that saves the workbook as a different name and then closes the workbook and quits excel. The problem is that I receive an error "Incorrect Function" just when excel quits. The workbooks close and the application closes fine, but the error is constant.

I found that this error only occurs when the code is executed through the custom ribbon control. If I execute this code through the macros area, or through VBA editor, I don't receive the error.

Any idea why this would happen when executed through the ribbon control? Below is the code

Sub FinishOU(control as iRibbonControl)
    Dim MonthEndDate As Range, wb1 As Workbook
       
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
        
        Set wb1 = ActiveWorkbook
        Set MonthEndDate = Sheet1.Range("C2:C3")
        With wb1
            .Save
            MonthEndDate.Value = MonthEndDate.Value
            MonthEnd = Format(Sheet1.Range("C3"), "MMYY")
            .SaveAs MonthEndDir & MonthEnd & "\" & "OU-" & MonthEnd
        End With
        Set MonthEndDate = Nothing
       
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .Quit
        wb1.Close False
    End With
    
    
End Sub

Re: Multiple Criteria Lookup for Conditional Formatting

works like a charm! Thanks for the lesson.

JMAN

Hello again OzGrid!

Attached you will find a sample for a visual....

Basically what I need to do is apply a conditional formatting formula rule so I can highlight certain rows, based on two criteria.

Sheet1 has multiple columns of data. The two columns that need to be checked are:

"state" column and "CTCorp" Column.

Sheet2 has a list of addresses. The only column I need to reference on this sheet is the "state" column.

Here is what I need done. I would like conditional formatting to look at sheet2's first entry which is "AK". Look at sheet1 to see if we have "AK" listed. If we do, then look at the "CT Corp" column. If that reads "Yes", then highlight the address on sheet2. I need this done for all the states listed. I used "AK" as an example.

I've been playing with INDEX and MATCH, but I haven't figured out how check for the second criteria yet.

Thanks for your help (AGAIN),

JMAN

Re: Find dates within date range

Quote from daddylonglegs;530024

Small change to PCI's formula will check whether any of the three dates fall in the range

=(B7<=EndDate)*(B7>=StartDate)+(C7<=EndDate)*(C7>=StartDate)+(G7<=EndDate)*(G7>=StartDate)=1

Worked like a charm, thanks guys.

JMAN

Re: Find dates within date range

The first formula works, but it only checks C7. I need the formula to look at all the date ranges and tell me if any of the dates are within the 12/1/10 to 12/31/10 range.

The second formula looked as if it were checking all the date ranges, but it just didn't give me the result I need.

Re: Find dates within date range

PCI, I do need all date columns tested. I looked at your sample, but the second formula (the one that tests all columns) returns all "False", when there is actually a date in that range.

I was hoping to get a little help with a formula to check a series of date ranges to see if they fall between two dates. The ultimate goal is to apply a filter to only show the rows that contain the date that fails within the two date ranges.

For instance, this month I would run a report for December. So, my date ranges would be from 12/1/10 to 12/31/10 (these two date ranges are in two hidden cells. These two cells are formulas that produce the two date ranges).

I would want a formula that checks each range to see if I have an item that expires within this range. I've attached a sample for better clarification.

Thanks again,

JMAN