Data validation with a named range and conditional formatting

Topic

New Reply

I’m working with someone who has a spreadsheet using a named range for data entry validation. It accepts blanks but it should not. WHen I change this to a list, it doesn’t accept the blanks and that’s what she wants but she wants to use the named range which I can’t get to work.

Then there are other fields on the spreadsheet which should have something entered into them but which some folks leave blank. I’ve tried like crazy to code a conditional format to turn a cell red when something is expected there but the cell is blank. I can get it to work for one cell but not for the entire range. My condition is, in English, if cell a2 is not blank and cell g5 is blank, turn g5 red. The same thing is true for a3 and g3, a4 and g4, etc. Can’t get it to work.

A nudge in the right direction is all I think I need.:confused:

Reply | Quote

Replies

Do you have blank cells in the named range? Turn off the Ignore blanks option in the data validation

for the CF, select all the cells in column G that you want and the formula is (assuming G2 is the active cell)
=AND($A2″”,$G2=””)
note there are no $ signs in front of the row numbers (they are not strictly necessary in front of the column letters here)

Reply | Quote

Thanks! I had put a space between the quotation marks. Should have been null!

Thank you so much!;)

Reply | Quote