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
Ken Johnson said:
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:
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 Sheet
CODES & 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
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
