Format a Table Cell based on another cell value, without VBA

[Nicha]

Hi Excel community!

I have a table where I would like to be able to format the value of the [Process] column according to the value of the [Type_Cli] column, as shown below:

Case [Type_Cli] is 'F' -> Format shoult be : "P000000000"; Star with Letter 'P', followed by nine digits'
Case [Type_Cli] is 'J' -> Format shoult be : "J000000000"; Star with Letter 'P', followed by nine digits'

Example values:

If [Type_Cli] is equal to 'F'-> and user type the number 12345, The cell must show : P000012345
If [Type_Cli] is equal to 'J'-> and user type the number 2345, The cell must show : J000002345

Process [Format]	Process - Value	Tipo_Cli
J000000000	J000002345	J
P000000000	P000012345	F

 

[etaf]

use a conditional formatting rule
use custom formatting
P000000000
with 2 rules - 1 for J and 1 for F

Book2
	A	B	C
1	process format	value	tipo
2		J000000123	J
3		P000000012	f
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B3	Expression	=C2="F"	text	YES
B2:B3	Expression	=C2="J"	text	YES

 

[Nicha]

Hi @etaf, Thank you. With your solution, when I insert a numeric value in column B, this will automatically format that cell as pretended?
Forgive me please; I didn't fully understand witch are the best method.
Can you explain step by step?

Thank you once more.

P.s: I forgot to mention, but if Column [C] has no value, Column must show nothing.
Byst regards.

 

[etaf]

i tried to show the screen shot for setting up
however
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B2:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=C2="F"

Format [Number, Font, Border, Fill]

use custom formatting -->> Number and use
P000000000

choose the format you would like to apply when the condition is true
OK >> OK

i then setup another rule for C2="J"
and used

use custom formatting -->> Number and use
J000000000

if cell in B is blank then you get a blank

Book2
	A	B	C
1	process format	value	tipo
2			J
3		P000054321	f
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B3	Expression	=C2="F"	text	YES
B2:B3	Expression	=C2="J"	text	YES

 

[Nicha]

i tried to show the screen shot for setting up
however
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B2:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=C2="F"

Format [Number, Font, Border, Fill]

choose the format you would like to apply when the condition is true
OK >> OK

i then setup another rule for C2="J"
and used

if cell in B is blank then you get a blank

Book2
	A	B	C
1	process format	value	tipo
2			J
3		P000054321	f
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B3	Expression	=C2="F"	text	YES
B2:B3	Expression	=C2="J"	text	YES

Perfect.
Manny Thank's

 

[etaf]

you are welcome, glad it worked ok for you