Thanks again, Rory. Once again, you came through!
Posts by JMAN
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.
Re: Compare previous years earnings to current year by month
That worked great, thanks!
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.
Re: Defining the same variable, repetitively
Yes, makes perfect sense. Thanks for the input, I appreciate it.
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:
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 controlQuote from rory;546969
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 controlQuote 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
I even tried changing the sub just FinishOU() and then create another sub named:
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 codeCode
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.
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),
Re: Find dates within date rangeQuote from daddylonglegs;530024
Small change to PCI's formula will check whether any of the three dates fall in the range
Worked like a charm, thanks guys.
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.