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

--
Message posted via http://www.officekb.com

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

On Oct 10, 7:49 pm, "BNT1 via OfficeKB.com" <u19326@uwe wrote:
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

--
Message posted viahttp://www.officekb.com

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 DataValidation
Allow: Custom
Formula:

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

OK out

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
ups.com...
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

On Oct 10, 7:49 pm, "BNT1 via OfficeKB.com" <u19326@uwe wrote:
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

--
Message posted viahttp://www.officekb.com

thanks to you all

have used the custom validation and formula, then copied sheet

regards

T. Valko wrote:
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 DataValidation
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]
--
Message posted viahttp://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200710/1

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:79af74b807597@uwe...
thanks to you all

have used the custom validation and formula, then copied sheet

regards

T. Valko wrote:
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 DataValidation
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]
--
Message posted viahttp://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200710/1