condition formula

[santoshloka]

Beam	S1	S1a	S2	S2a	S3	S3a
Pier Nos	P1	P2	P3	P4	P5	P6
	Input-1	Input-2
	P1-P2	S1

Looking for conditional formula

I will change values manually in Input-1 and input-2,if P1-P2 =S1 OR P2-P3=S3a OR P3-P4=S3...etc
Based on below cell values in Input-1,Input-2
find the "Pier Nos" and match the color in "Beam"

 

[earthworm]

tR

Beam	S1	S1a	S2	S2a	S3	S3a
Pier Nos	P1	P2	P3	P4	P5	P6
	Input-1	Input-2
	P1-P2	S1

Looking for conditional formula

I will change values manually in Input-1 and input-2,if P1-P2 =S1 OR P2-P3=S3a OR P3-P4=S3...etc
Based on below cell values in Input-1,Input-2
find the "Pier Nos" and match the color in "Beam"

Try below

Book1
	A	B	C	D	E	F	G
1	Beam	S1	S1a	S2	S2a	S3	S3a
2	Pier Nos	P1	P2	P3	P4	P5	P6
3
4	Input-1	Input-2
5	P1-P2	S1
Sheet1

Cell Formulas
Range	Formula
B5	B5	=VLOOKUP(A5,{"P1-P2","S1";"P2-P3","S2";"P3-P4","S3"},2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:G2	Expression	=OR(B2=LEFT($A$5,2),B2=RIGHT($A$5,2))	text	NO
B1:G1	Expression	=B1=$B$5	text	NO
B1:G1	Cell Value	=$B1=$B$5	text	NO

 

[santoshloka]

Hey thanks for yr responds

you saved tons of my time..S3a is not working(Color not showing).. remaining all are fine
i dont know where to add this formula B5==VLOOKUP(A5,{"P1-P2","S1";"P2-P3","S2";"P3-P4","S3"},2,0)
Because B5 i will change manually .

i have up to P1 to P350-->B2 TO G2 Same,,can i use same condition,,i just added up to P1 to P6...P350

if possible please share excel file for better understanding

 

[earthworm]

Hey thanks for yr responds

you saved tons of my time..S3a is not working(Color not showing).. remaining all are fine
i dont know where to add this formula B5==VLOOKUP(A5,{"P1-P2","S1";"P2-P3","S2";"P3-P4","S3"},2,0)
Because B5 i will change manually .

i have up to P1 to P350-->B2 TO G2 Same,,can i use same condition,,i just added up to P1 to P6...P350

if possible please share excel file for better understanding

Just copy and paste into excel.