# Posts by JMAN

• ## Formula that doesn't include blanks

Both solutions work - Thank you!

• ## Formula that doesn't include blanks

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.

## Files

• OzSample.xlsx

• ## Only allow "Y" or "N" entries in cells without blanks

Re: Only allow &quot;Y&quot; or &quot;N&quot; entries in cells without blanks

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

• ## Only allow "Y" or "N" entries in cells without blanks

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

• ## Compare previous years earnings to current year by month

Re: Compare previous years earnings to current year by month

That worked great, thanks!

• ## Compare previous years earnings to current year by month

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

• ## Defining the same variable, repetitively

Re: Defining the same variable, repetitively

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

JMAN

• ## Defining the same variable, repetitively

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:

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

• ## Quitting Excel Application through ribbon control

Re: Quitting Excel Application through ribbon control

Quote from rory;546969

Change the callback to:

Code
1. Sub ActivateOUCompletion(control as iRibbonControl)
2. Application.Ontime Now(), "FinishOU"
3. 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.

• ## Quitting Excel Application through ribbon control

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

Code
1. Sub FinishOU (control as iRibbonControl)

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

Code
1. Sub ActivateOUCompletion (control as iRibbonControl)
2. Call FinishOU
3. End sub

• ## Check if source range is empty before copying to target workbook

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?

• ## Quitting Excel Application through ribbon control

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

• ## Multiple Criteria Lookup for Conditional Formatting

Re: Multiple Criteria Lookup for Conditional Formatting

works like a charm! Thanks for the lesson.

JMAN

• ## Multiple Criteria Lookup for Conditional Formatting

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.

JMAN

• ## Find dates within date range

Re: Find dates within date range

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

• ## Find dates within date range

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.

• ## Find dates within date range

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.

• ## Find dates within date 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

• ## Workbook reference returns runtime 1004

Re: Workbook reference returns runtime 1004

I got it to work by saving the Office 2003 created file (xls) as a macro enabled workbook (xlsm). Once I did this, the code ran fine. I'm assuming this has something to do with the rows.count (65536) not being the same in the 2003 version as in the 2007 version (1048576).

• ## Workbook reference returns runtime 1004

I have a macro that I created in Excel 2003 that no longer works in Excel 2007. I've tested several times and it works perfectly in 2003. The code is below.

I've highlighted the line that errors. The only way I can get this macro to continue is to actually select the workbook it is trying to reference so that it is the active workbook. I know I could easily fix this by activating the workbook, but I've never had to do this. Any ideas?

JMAN