validation - conditional format

[BNT1 via OfficeKB.com]

Hi

I require that a cell range , say, A1:A23, can only accept, "H","S","Y","N"
or "O" and nothing else. The file is 52 sheets and will be applied to all
sheets

Can this be achieved by conditional formating or has it got to be
validation/list etc?

thanks in advance

brian

 

[Pete_UK]

Conditional formatting has got nothing to do with it !! CF allows you
to change the appearance of the cell (bold, italic, underline, colours
etc) if some condition is met, and in XL2003 or earlier you can have
up to 3 different conditions monitored in each cell. This has got
nothing to do with restricting the items that can be put into a cell,
which, as you surmise, is something you do with Data | Validation.

However, you can save a lot of time by grouping all the sheets
together (right-click on a sheet tab, or click on the first sheet,
hold down SHIFT and click on the last sheet tab then release SHIFT)
and then set up the data validation in the cells A1:A23. When
finished, ungroup the sheets (right-click on a tab), and the DV will
have been applied to that range in all the sheets.

Hope this helps.

Pete

 

[T. Valko]

However, you can save a lot of time by grouping all the sheets

together (right-click on a sheet tab, or click on the first sheet,
hold down SHIFT and click on the last sheet tab then release SHIFT)
and then set up the data validation in the cells A1:A23.

Can't apply validation with sheets grouped, at least, not in Excel 2002.

You can apply the validation to one sheet then copy it and paste it to the
remaining grouped sheets.

A1:A23, can only accept, "H","S","Y","N"or "O" and nothing else.

I assume you explicitly mean those uppercase letters.

Select the range A1:A23
Goto Data>Validation
Allow: Custom
Formula:

=AND(LEN(A1)=1,COUNT(FIND(A1,"HSYNO")))

OK out

 

[BNT1 via OfficeKB.com]

thanks to you all

have used the custom validation and formula, then copied sheet

regards

However, you can save a lot of time by grouping all the sheets
together (right-click on a sheet tab, or click on the first sheet,
hold down SHIFT and click on the last sheet tab then release SHIFT)
and then set up the data validation in the cells A1:A23.

Can't apply validation with sheets grouped, at least, not in Excel 2002.

You can apply the validation to one sheet then copy it and paste it to the
remaining grouped sheets.

A1:A23, can only accept, "H","S","Y","N"or "O" and nothing else.

I assume you explicitly mean those uppercase letters.

Select the range A1:A23
Goto Data>Validation
Allow: Custom
Formula:

=AND(LEN(A1)=1,COUNT(FIND(A1,"HSYNO")))

OK out

Conditional formatting has got nothing to do with it !! CF allows you
to change the appearance of the cell (bold, italic, underline, colours

[quoted text clipped - 31 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

thanks to you all

have used the custom validation and formula, then copied sheet

regards

However, you can save a lot of time by grouping all the sheets
together (right-click on a sheet tab, or click on the first sheet,
hold down SHIFT and click on the last sheet tab then release SHIFT)
and then set up the data validation in the cells A1:A23.

Can't apply validation with sheets grouped, at least, not in Excel 2002.

You can apply the validation to one sheet then copy it and paste it to the
remaining grouped sheets.

A1:A23, can only accept, "H","S","Y","N"or "O" and nothing else.

I assume you explicitly mean those uppercase letters.

Select the range A1:A23
Goto Data>Validation
Allow: Custom
Formula:

=AND(LEN(A1)=1,COUNT(FIND(A1,"HSYNO")))

OK out

Conditional formatting has got nothing to do with it !! CF allows you
to change the appearance of the cell (bold, italic, underline, colours

[quoted text clipped - 31 lines]