scoochie

This is part of the date formatting query that I had...though I started a new thread as it is a bit different. I am trying to achieve, using conditional formatting, to out put a true/false condition by examining up to 4 cells that have only the month of the year in them rather than a month and day.

For example, I would like to apply a format to A1 if A2 OR A3 or A4 or A5 is equal to "this month". I would also like to know if it is possible to have a cell on a separate worksheet equal a cell from another with respect to format only. As I know you cannot "conditionally" format a cell on one worksheet to examine cells of another, I would like to set up the whole conditioning process on one sheet, and merely have a cell on another worksheet follow the formatting rules of the cell containing the conditions (maybe I am trying to break the rules?)

The purpose is to have an "annunciated cell" on a scheduling chart that merely changes format because of an event that is due any day this month (no particular day).

Thanks for any ideas. I have been reading these forums and only a member for a few days and it has helped me enormously...

Best Regards,

SCOOCHIE

scoochie

I have exhausted my search options on this one....guess nobody has any ideas on this one or else maybe not possible?

Glaswegian

Hi scoochie

I'm heading for some sleep, but had a quick look at your problem. One thing that did occur to me was that, to be able to use conditional formatting on a cell on another sheet, you need to name your ranges, then just use the name in your custom formula.

You might also want to have a look at Chip Pearsons's site for some ideas.

http://www.cpearson.com/excel/cformatting.htm

I'm not that good at formulae, but could probably come up with some code that would do the same thing - but perhpas you want to stick with CF.

Regards

scoochie

Thanks for the post Glaswegian. As a novice, I found the link provided informative and thought provoking. It is funny how little i have known about excel and used it for so many years; now, It almost consumes me to solve little quirky problems to the point where I may be sitting in the toilet or driving to work thinking about it and have an "epiphany" where I can't wait to get back top the office or home to attempt what came to me.

I am not shy to use VBA code either...I think I have used some where not necessary and would welcome the use where actually needed.

Thanks

dheerajnagpal

Hi Soochie,

Yes, your first requirement can be solved by going to the conditional formatting for the cell, choosing that formula as =IF($A$3=MONTH(NOW()),TRUE,IF($B$3=MONTH(NOW()),TRUE,FALSE))
and setting the pattern.

The above one will check whether the value of A3 or B3 is same as current month (Month() returns values 1-12) then it applies the formatting specified.

Hope this helps.
PS - Try referencing other worksheets in place of A1 and B1. It might also work but i havent tried it.

scoochie

Thanks,

I have played with this a bit and it looks as though I can supplement it to suit my needs. I cannot get it to work by entering the text though for the month. I have to enter 1 - 12 in order to fet the true false condition. Is there a way I can modify the formula or format the cells containg the month to recognize the text (ie jan, feb, mar, apr etc) as the numeric values of 1 - 12 seemingly needed by the present formula? Any ideas?

dheerajnagpal

You can use Vlookup and create a table of Jan, feb, Mar etc with values 1,2,3

Please find the attached sheet. I made it in a hurry but should suffice your requirement.

Attached Files

Book1.zip (1.5 KB, 5 views)

scoochie

Thanks for the example. Please see attached where I have expanded on your formula and implemented into a worksheet. I am having a problem however. What I am trying to achieve is the ability to populate 1 to 4 cells with a month for 7 separate events. The idea is tha the event could be quarterly, bi annual, or annual. This would mean that depending on the event it may have a date month listed in 1, 2, or all four cells. The problem that I am having seems to be the return result of N/A rather than a numeric in the cells I am referencing to control the conditionaly formated cells to show whether the event is in the current month. I am also guessing that the order of the examination in the formula to produce the numeric in the cell to be refenced by the conditioanl formulas follows order and if the first cell is blank, it hangs.

Could you take a look and see if you can see what I am talking about? If you clear all the months from the input table and enter the current month in the 4th cell, you will see that the associated conditional cell does not activate. I need to manually enter an active month into the first cell, then the second, then , the third, before the formula will follow through to examine the last cell.

Thanks a lot for your assistance on this it is a great learning experience for me.

Attached Files

BookMonthCon.zip (3.5 KB, 2 views)

dheerajnagpal

Hi Soochie,

Ok, Lets begin with your problem.

You are saying that you are getting NA in case you are not entering any value in the cells to examine.
That happens because when it tries to match the cell value with the list, it doesnt find any value corrosponding to the blank and hence returns #NA (Not Available.

You can put in an if condition in front to check for the presence of value and to match condition only in that case. Else put in a 0.
I have added another value in lookup for 0 that returns a 0. Remember, the lookup table has to be sorted in increasing order hence 0 should be the first value always in any lookup of this sort.

I am modifying the sheet accordingly. Check it and let me know whether i have understood the probem correctly or not.

Attached Files

BookMonthCon.zip (3.3 KB, 10 views)

scoochie

That is exactly the solution I needed....and I believe I am starting to understand exactly how it works....thanks for you help. I will continue to develop my spreadsheet and be sure to post any obstacles that I cannot overcome.