Hi, I'm working on a project at work and am pulling my hair out with some conditional formatting.
First of all I added Data Validation to two columns, the second column being conditional DV and this works a treat. The user pick from one of 4 options in first column and the options available int he next column are dependant upon their first selection. I was aware however that a user could work backwards i.e make a selection in the secong column first and this would then not match the correct option in the first column - SO I decided to add CONDITIONAL FORMATTING to the "Action" column, so that the text in this column turns Red if the wrong selection is made. This also works as I would like.
Now to the problem: in the third Column named "comments", this is where the user can just make any general notes or comments and I have done nothing to this column, however I have noticed that if a user attempts to write a comment in here and the cell is blank, their text turns Red and is effectively added to my range formula from "action" columns Conditional formatting. It's driving me crazy trying to rectify this. If there is already text in the comments column, then the user can add further text and it stays unformatted. The problem seems to be just with blank cells.



Does anybody have any ideas as to why this happens and how I can resolve it?
I have replacing any blank celles in "comments " column with a zero, however this is not an ideal solution. I have also switched off the "Extend data range formats and formulas" from the excel options menu, but yet again this is not an ideal solution because it only works on my computer and numerous users will be using the spreadsheet.

I have attached a small sample file of this problem, was wobdering if anyone could come up with an amendment to my formula that lies in the Data Validation in "action" column? At the end of formula where I have a "false" argument, this seems to keep changing all the time when you view it! also the range changes and adds in ceels from the comments column. I have just checked my sample sheet (attached) and this still happens, however guess what, the text entered in blank cells is not turning red now, however I know it will again because ive had the problem all week

Thanks in advance

Attached Files

Conditonal formatting and DV sample.xlsx (13.1 KB, 13 views)

Hi WRighty50
I'd try making your CF formula a little different.
You have
=ISERROR(MATCH(B2,INDIRECT(SUBSTITUTE(A2," ","")),FALSE))
and I'd try
=ISERROR(MATCH($B2,INDIRECT(SUBSTITUTE($A2," ","")),FALSE))

BTW - Column 3 didn't turn red on your attached.

What is the FALSE for in I thought the switches for MATCH were 1,0 and -1?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi MarvinP,
thanks for your reply. The sample I sent wasn't very well thought out, in practice the spreadsheet has numerous columns and rows, but I omitted all of the non relevant columns and because of this the problem doesnt show. I will send over a reworked example with columns either side and then when you enter text into my "comments" column, you will see that the text goes red.
I have just tried your suggestion regarding the Absolute signs and I think this works (so thanks) however I'm a bit concerned that after entering comments, when you look at the conditional formtting, the range increases. EG if you enter text in cell D5, this gets added to the range (ok it hasnt turned red but this range will keep increasing despite the appliction of $ signs). So I'm not sure if this will work long term, as the range will keep increasing everytime someone adds a comment.

The revised file I will add shortly has some extra columns either side, and I have not added in the $ signs, so that you can see the problem. If you then change the formula as you suggested, yes it does work but bear in mind my comments above about the ranges changing.

Btw thanks ever so much for the response and would welcome any other advice

Wrighty50

Hi Pecoflyer,
do you think my formula is wrong? If so have you any suggestions for an alternative formula to the one I have?

I used the FALSE because it's part of the formula that I've used in the conditional data validation (before doing the conditional formatting), if you look at the DV I've done on the actions and status' columns. However that doesnt mean im right, in fact I must be doing something wrong with my conditionl formatting. I'm posting a revised example and any solution appreciated

Thanks

Hi all,
attached is a reworked example, with additional columns either side. Now you can see the problem when you enter text into a blank cell in the "comments" column. When I try MarvinP solution with the absolute $ signs it does seem to work, however note that the range increases when you review the formula in conditional formatting

Thanks

Attached Files

Conditonal formatting and DV sample 2 Absolutes.xlsx (13.6 KB, 9 views)

Hi wrighty50,
Pecoflyer didn't like the "False" because the MATCH() function is looking for a -1,0 or 1 for the third argument. Your "False" in your CF formula will translate to a zero and all if fine for an exact match. So you can use a zero or False in your case.

I'll look at your new attach file now but wanted to get the False vs Zero partial question answered first.

Hi MarvinP,

ok thanks, so my formula regarding the FALSE is ok then, and yes I'm only dealing with exact matches for the data validation, so should be fine on that front.

Yes much appreciated if you could now look at my revised example

Thanks

You said the CF formula works when there is a $ in it.

The dollar sign means to always use that column (or row) for the comparison. It sounds like you want to use a different CF formula in other columns?

I'm not sure where you are with your question now.

I hope you understand the $ in the formula. If not a very simple explaination can be found at http://www.homeandlearn.co.uk/excel2...l2007s7p5.html

Hi again,
apologies if Im not explaining myself too well here

Ok, Im about to send over a new file now. I have added in the $ sign as you suggested. If you go to cell B6 and change the selection to "CASE UPDATE" you will see that the adjacent cell turns red - this is correct and what I would like because the user has selected an incorrect option i.e. "Abandoned" is not one of the eligible options for "case update" - so all good. However if you now type some text in the blank "comments" cell D6, the text is also red and I do not want this. The text in the comments column needs to be black all the time, and this only happens when entering text into a blank cell.

I do understand the absolute $ sign and use it regularly in other formula, I just can't get the desired result on this spreadsheet. You'll notice that if you enter text into D6, without first following my above intructions, then the text will be black. So the $ sign works in those circumstances but not if the user makes an incorrect selection first in the "status" or "actions" cells.

Thanks

ok heres new attachment with the $ signs added
thanks

Attached Files

Conditonal formatting and DV sample 2 Absolutes.xlsx (13.6 KB, 10 views)

MarvinP,

I have noticed however that if the user corrects an incorrect selection from the dropdowmns, then the comments will revert back to black! So it kinda works then because a user should not be leaving their spreadsheet with incorrect selections (thats the whole idea of why Ive done this).

It's still odd why the Comments column changes to red under these circumstances because I have not and do not wish to apply any formatting in that column

Thanks again - I appreciate you guys taking a look