Conditional Format to Retain Text Colour from a List on another Sh

[Dasha]

Hi In XP Excel 2000 I Have a Vallidated List on a Sheet named (CODES &
Abrieviatons ) I have Named that List as (CODES_Abriev's)
I have 3 groups of "Decribing Abrieviations" colour Coded Eg: B7 to B9 =
Describing different types of Pipe Laying EG:

Water-Pipes is Green: [ Pipes-Water/{1}EW) ]
Pipes Storm Water: [ Pipes- Storm/{}) ] and
Sewer Pipes as [ Pipes- Sewer/{}) ] Then the List contiues:

Pit's is B10 to B27 Blue

Then "Pieces": B28 to B40 in Orange

In the list they are Colour Coded & Formatted ) a Cell in a blank Cell as an
Indicator when scrolling the Dropdown List showing ***** in Blue Meaning the
Start of Pit's Data: Then ***** In a Cell Before "Pieces" How Do I get each
one to enter in their original Colour's after selecting it in the Dropdown
List on SheetCODES & Abrieviatons ) And enter it in the (TENDER TEMPLATE x
1) Range Named (CODES B15 to B150) in it's original Colour Code? Any help
would be gladly recieved Guy's thank you

 

[Learning Excel]

Could you translate that to English ?

 

[Ken Johnson]

Hi In XP Excel 2000 I Have a Vallidated List on a Sheet named (CODES &
Abrieviatons ) I have Named that List as (CODES_Abriev's)
I have 3 groups of "Decribing Abrieviations" colour Coded Eg: B7 to B9 =
Describing different types of Pipe Laying EG:

Water-Pipes is Green: [ Pipes-Water/{1}EW) ]
Pipes Storm Water: [ Pipes- Storm/{}) ] and
Sewer Pipes as [ Pipes- Sewer/{}) ] Then the List contiues:

Pit's is B10 to B27 Blue

Then "Pieces": B28 to B40 in Orange

In the list they are Colour Coded & Formatted ) a Cell in a blank Cell as an
Indicator when scrolling the Dropdown List showing ***** in Blue Meaning the
Start of Pit's Data: Then ***** In a Cell Before "Pieces" How Do I get each
one to enter in their original Colour's after selecting it in the Dropdown
List on SheetCODES & Abrieviatons ) And enter it in the (TENDER TEMPLATE x
1) Range Named (CODES B15 to B150) in it's original Colour Code? Any help
would be gladly recieved Guy's thank you

Maybe conditional formatting in the cells with the data validation
dropdown.

Con Format 1: FormulaIs: =LEFT($B15,3)="Pip", Format:Green
Con Format 2: FormulaIs: =LEFT($B15,3)="Pit", Format:Blue
Con Format 3: FormulaIs: =LEFT($B15,3)="Pie", Format:Orange

Ken Johnson

 

[Dasha]

Could you translate that to English ?
-
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.

 

[Dasha]

Thank's Very much Ken and thank's for your time, Just what I needed, Dasha

Hi In XP Excel 2000 I Have a Vallidated List on a Sheet named (CODES &
Abrieviatons ) I have Named that List as (CODES_Abriev's)
I have 3 groups of "Decribing Abrieviations" colour Coded Eg: B7 to B9 =
Describing different types of Pipe Laying EG:

Water-Pipes is Green: [ Pipes-Water/{1}EW) ]
Pipes Storm Water: [ Pipes- Storm/{}) ] and
Sewer Pipes as [ Pipes- Sewer/{}) ] Then the List contiues:

Pit's is B10 to B27 Blue

Then "Pieces": B28 to B40 in Orange

In the list they are Colour Coded & Formatted ) a Cell in a blank Cell as an
Indicator when scrolling the Dropdown List showing ***** in Blue Meaning the
Start of Pit's Data: Then ***** In a Cell Before "Pieces" How Do I get each
one to enter in their original Colour's after selecting it in the Dropdown
List on SheetCODES & Abrieviatons ) And enter it in the (TENDER TEMPLATE x
1) Range Named (CODES B15 to B150) in it's original Colour Code? Any help
would be gladly recieved Guy's thank you

Maybe conditional formatting in the cells with the data validation
dropdown.

Con Format 1: FormulaIs: =LEFT($B15,3)="Pip", Format:Green
Con Format 2: FormulaIs: =LEFT($B15,3)="Pit", Format:Blue
Con Format 3: FormulaIs: =LEFT($B15,3)="Pie", Format:Orange

Ken Johnson

 

[Dasha]

Ken Can I Impose once more? I Tried Your Formula's and I must be doing
something wrong I will show you how here I did it! I don't know what the
LEFT refers too or: (B15,3) I assume B15 was a Demo Cell Range you made up
for the excercise? Here Goes I entered:

Format 1: =LEFT($B7:$B9)="Pipes"= and Formated as Green.
Format 2: =LEFT($B10:$B27)="Pipes"= and Formated as Blue.
Format 3: =LEFT($B28:$B40)="Pipes"= and Formated as Orange.
Dasha

Hi In XP Excel 2000 I Have a Vallidated List on a Sheet named (CODES &
Abrieviatons ) I have Named that List as (CODES_Abriev's)
I have 3 groups of "Decribing Abrieviations" colour Coded Eg: B7 to B9 =
Describing different types of Pipe Laying EG:

Water-Pipes is Green: [ Pipes-Water/{1}EW) ]
Pipes Storm Water: [ Pipes- Storm/{}) ] and
Sewer Pipes as [ Pipes- Sewer/{}) ] Then the List contiues:

Pit's is B10 to B27 Blue

Then "Pieces": B28 to B40 in Orange

In the list they are Colour Coded & Formatted ) a Cell in a blank Cell as an
Indicator when scrolling the Dropdown List showing ***** in Blue Meaning the
Start of Pit's Data: Then ***** In a Cell Before "Pieces" How Do I get each
one to enter in their original Colour's after selecting it in the Dropdown
List on SheetCODES & Abrieviatons ) And enter it in the (TENDER TEMPLATE x
1) Range Named (CODES B15 to B150) in it's original Colour Code? Any help
would be gladly recieved Guy's thank you

Maybe conditional formatting in the cells with the data validation
dropdown.

Con Format 1: FormulaIs: =LEFT($B15,3)="Pip", Format:Green
Con Format 2: FormulaIs: =LEFT($B15,3)="Pit", Format:Blue
Con Format 3: FormulaIs: =LEFT($B15,3)="Pie", Format:Orange

Ken Johnson

 

[Ken Johnson]

Ken Can I Impose once more? I Tried Your Formula's and I must be doing
something wrong I will show you how here I did it! I don't know what the
LEFT refers too or: (B15,3) I assume B15 was a Demo Cell Range you made up
for the excercise? Here Goes I entered:

Format 1: =LEFT($B7:$B9)="Pipes"= and Formated as Green.
Format 2: =LEFT($B10:$B27)="Pipes"= and Formated as Blue.
Format 3: =LEFT($B28:$B40)="Pipes"= and Formated as Orange.
Dasha

Hi In XP Excel 2000 I Have a Vallidated List on a Sheet named (CODES &
Abrieviatons ) I have Named that List as (CODES_Abriev's)
I have 3 groups of "Decribing Abrieviations" colour Coded Eg: B7 to B9 =
Describing different types of Pipe Laying EG:
Water-Pipes is Green: [ Pipes-Water/{1}EW) ]
Pipes Storm Water: [ Pipes- Storm/{}) ] and
Sewer Pipes as [ Pipes- Sewer/{}) ] Then the List contiues:
Pit's is B10 to B27 Blue
Then "Pieces": B28 to B40 in Orange
In the list they are Colour Coded & Formatted ) a Cell in a blank Cell as an
Indicator when scrolling the Dropdown List showing ***** in Blue Meaning the
Start of Pit's Data: Then ***** In a Cell Before "Pieces" How Do I get each
one to enter in their original Colour's after selecting it in the Dropdown
List on SheetCODES & Abrieviatons ) And enter it in the (TENDER TEMPLATE x
1) Range Named (CODES B15 to B150) in it's original Colour Code? Any help
would be gladly recieved Guy's thank you

Maybe conditional formatting in the cells with the data validation
dropdown.

Con Format 1: FormulaIs: =LEFT($B15,3)="Pip", Format:Green
Con Format 2: FormulaIs: =LEFT($B15,3)="Pit", Format:Blue
Con Format 3: FormulaIs: =LEFT($B15,3)="Pie", Format:Orange

Ken Johnson

Hi Dasha,

Are the cells you are wanting to be conditionally formatted the same
cells with the data validation dropdown?

I thought that B15:B150 all had data validation using a list named
CODES_Abriev's on a sheet named CODES & Abrieviatons. I figured that
when one of your users used the data validation dropdown in any of the
cells in B15:B150 on the sheet named TENDER TEMPLATE x1 that the list
of choices consisted of items with first three characters being either
"Pip" (from "Pipes etc"), "Pit" (from "Pit's") or "Pie" (from
"Pieces").

If this is correct, then what I thought you could do was select
B15:B150 (the cells with the data validation), then go Format|
Conditional formatting.

The Conditional Formatting dialog that appears has a textbox on the
left with a dropdown. You should choose "Formula Is" for this textbox.

In the textbox immediately to its right you should type the formula...

=LEFT($B15,3)="Pip"

then click the Format button and adjust the green formatting.

With the above formula in place in the conditional formatting, say the
user clicks on B30 then selects Pipes-Water/{1}EW) from the data
validation dropdown. Excel then calculates the formula...

=LEFT($B30,3)="Pip"

Notice that the row number in the formula is 30, not 15. This is
because the row number 15 in the original formula did not have a $
sign to its left. This is how you control which cells Excel uses when
is tests for conditional formatting.

Since B30 contains "Pipes-Water/{1}EW)" , the formula LEFT($B30,3)
returns the first 3 characters of "Pipes-Water/{1}EW)", which is
"Pip".
So, the result of calculating =LEFT($B30,3)="Pip" is TRUE.
When a conditional format calculation is TRUE the chosen format, in
this case green text or background, is applied.

Similarly for the other two format conditions...

The formula for the blue formatting is =LEFT($B15,3)="Pit", and for
the orange formatting =LEFT($B15,3)="Pie"


If my interpretation of what you have and what you are trying to
achieve is not correct, then let me know, and we can have another go.

Ken Johnson

 

[Dasha]

I am Sorry Ken I am Making a mess of this' let me clear it up; all the
coulors etc are as you said' its the 'Refrences' thit i have you confused on!

The Defined 'List' Is In Sheet Named: 'CODES & ABRIEVIATIONS',
It is Named as 'CODES_List' and is this B7 -->B41
The three different coloured Groups are all in the 'List' together and in
their own colour Group as, Pipes: Green, Pits:Blue and Pieces:Orange!

The Dropdown list is up and working fine in the Sheet TEMPLATEx1 in the
Column B under heading of 'PP&PCodes and Colours' in cells B15 -->B150 but
when i enter one from theDropdown it is always in 'black '
So Ken if you can tell me how to get the Colour Formtting to work via Cond
Form and insert in the 'PP&PCodes and Colours' column in thier respective
formatted colours instead of all black we will be lookin good I have removed
the other Leadins from the CODES_ List eg ****** they are all gone, again I
am Sorry for all this thanks for your patience. Dasha

Ken Can I Impose once more? I Tried Your Formula's and I must be doing
something wrong I will show you how here I did it! I don't know what the
LEFT refers too or: (B15,3) I assume B15 was a Demo Cell Range you made up
for the excercise? Here Goes I entered:

Format 1: =LEFT($B7:$B9)="Pipes"= and Formated as Green.
Format 2: =LEFT($B10:$B27)="Pipes"= and Formated as Blue.
Format 3: =LEFT($B28:$B40)="Pipes"= and Formated as Orange.
Dasha

Hi In XP Excel 2000 I Have a Vallidated List on a Sheet named (CODES &
Abrieviatons ) I have Named that List as (CODES_Abriev's)
I have 3 groups of "Decribing Abrieviations" colour Coded Eg: B7 to B9 =
Describing different types of Pipe Laying EG:

Water-Pipes is Green: [ Pipes-Water/{1}EW) ]
Pipes Storm Water: [ Pipes- Storm/{}) ] and
Sewer Pipes as [ Pipes- Sewer/{}) ] Then the List contiues:

Pit's is B10 to B27 Blue

Then "Pieces": B28 to B40 in Orange

In the list they are Colour Coded & Formatted ) a Cell in a blank Cell as an
Indicator when scrolling the Dropdown List showing ***** in Blue Meaning the
Start of Pit's Data: Then ***** In a Cell Before "Pieces" How Do I get each
one to enter in their original Colour's after selecting it in the Dropdown
List on SheetCODES & Abrieviatons ) And entered it in the (TENDER TEMPLATE x1
The Range is Named (CODES_Abrievs and is Refers to B15 to B150) in it's original Colour Code? Any help
would be gladly recieved Guy's thank you

Maybe conditional formatting in the cells with the data validation
dropdown.

Con Format 1: FormulaIs: =LEFT($B15,3)="Pip", Format:Green
Con Format 2: FormulaIs: =LEFT($B15,3)="Pit", Format:Blue
Con Format 3: FormulaIs: =LEFT($B15,3)="Pie", Format:Orange

Ken Johnson

Hi Dasha,

Are the cells you are wanting to be conditionally formatted the same
cells with the data validation dropdown?

I thought that B15:B150 all had data validation using a list named
CODES_Abriev's on a sheet named CODES & Abrieviatons. I figured that
when one of your users used the data validation dropdown in any of the
cells in B15:B150 on the sheet named TENDER TEMPLATE x1 that the list
of choices consisted of items with first three characters being either
"Pip" (from "Pipes etc"), "Pit" (from "Pit's") or "Pie" (from
"Pieces").

If this is correct, then what I thought you could do was select
B15:B150 (the cells with the data validation), then go Format|
Conditional formatting.

The Conditional Formatting dialog that appears has a textbox on the
left with a dropdown. You should choose "Formula Is" for this textbox.

In the textbox immediately to its right you should type the formula...

=LEFT($B15,3)="Pip"

then click the Format button and adjust the green formatting.

With the above formula in place in the conditional formatting, say the
user clicks on B30 then selects Pipes-Water/{1}EW) from the data
validation dropdown. Excel then calculates the formula...

=LEFT($B30,3)="Pip"

Notice that the row number in the formula is 30, not 15. This is
because the row number 15 in the original formula did not have a $
sign to its left. This is how you control which cells Excel uses when
is tests for conditional formatting.

Since B30 contains "Pipes-Water/{1}EW)" , the formula LEFT($B30,3)
returns the first 3 characters of "Pipes-Water/{1}EW)", which is
"Pip".
So, the result of calculating =LEFT($B30,3)="Pip" is TRUE.
When a conditional format calculation is TRUE the chosen format, in
this case green text or background, is applied.

Similarly for the other two format conditions...

The formula for the blue formatting is =LEFT($B15,3)="Pit", and for
the orange formatting =LEFT($B15,3)="Pie"


If my interpretation of what you have and what you are trying to
achieve is not correct, then let me know, and we can have another go.

Ken Johnson

 

[Ken Johnson]

I am Sorry Ken I am Making a mess of this' let me clear it up; all the
coulors etc are as you said' its the 'Refrences' thit i have you confused on!

The Defined 'List' Is In Sheet Named: 'CODES & ABRIEVIATIONS',
It is Named as 'CODES_List' and is this B7 -->B41
The three different coloured Groups are all in the 'List' together and in
their own colour Group as, Pipes: Green, Pits:Blue and Pieces:Orange!

The Dropdown list is up and working fine in the Sheet TEMPLATEx1 in the
Column B under heading of 'PP&PCodes and Colours' in cells B15 -->B150 but
when i enter one from theDropdown it is always in 'black '
So Ken if you can tell me how to get the Colour Formtting to work via Cond
Form and insert in the 'PP&PCodes and Colours' column in thier respective
formatted colours instead of all black we will be lookin good I have removed
the other Leadins from the CODES_ List eg ****** they are all gone, again I
am Sorry for all this thanks for your patience. Dasha

Hi Dasha,

The steps I outlined last time should achieve what you're trying to
do.

It might not be working because I could be wrong with what I think is
being entered into those cells (B15:B150) from the data validation
dropdown.

What exactly are all of the listed values that the user can choose
from when using the data validation dropdown in B15:B150?

Is it possible for you to email me a sample workbook with similar
setup?
You should be able to find my email address by viewing my profile.

Ken Johnson

 

[Dasha]

I Will Send Clips Ken Dasha

Hi Dasha,

The steps I outlined last time should achieve what you're trying to
do.

It might not be working because I could be wrong with what I think is
being entered into those cells (B15:B150) from the data validation
dropdown.

What exactly are all of the listed values that the user can choose
from when using the data validation dropdown in B15:B150?

Is it possible for you to email me a sample workbook with similar
setup?
You should be able to find my email address by viewing my profile.

Ken Johnson