find maximum value of one column for the non-blank cells in another c

[Sumanmathew]

please help me solve this. I want to find S6 ,T6,U6 etc.

wherever there are non-blank cells in column S, the corresponding values in column O has to be considered.

I want the maximum value to appear in S6.Here in this example 1600 in S6. In T6, the value should be 600.

i have given Data Validation in column O cells, so the values can change.

whenever nonblank cells comes in columns from S to X the max value(from column O) should be displayed in that yellow row.(S6 to X6)
please help me.

3ML_SMDB.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y
5						s1	s2	s3	s4	s5	s6
6						1600	600	400
7		20A				1
8		600A					1		1
9		32A				4
10		100A					4	5	4	2	2
11		32A				1	2
12		63A						2	1	3	5
13		400A				2	3	1	4	5	1
14		1600A				1
15
3ML_SMDB (2)

Cells with Data Validation
Cell	Allow	Criteria
O7:O15	List	=Data_Val!$A$3:$A$100

 

[awoohaw]

how about:

Mr Excel Questions 9.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y
1						s1	s2	s3	s4	s5	s6
2						1600	600	400
3		20A				1
4		600A					1		1
5		32A				4
6		100A					4	5	4	2	2
7		32A				1	2
8		63A						2	1	3	5
9		400A				2	3	1	4	5	1
10		1600A				1
11
12
Sheet3

Cell Formulas
Range	Formula
S2:U2	S2	=MAX(IFERROR((--(ISBLANK(S3:S11)=FALSE))*(1*LEFT($O$3:$O$11,LEN($O$3:$O$11)-1)),0))

 

[awoohaw]

you may have to must press the CNTL-SHFT-ENTER to actuate the array calculation.

 

[Sumanmathew]

i think i found a solution.

Yes, it is working. Thanks a lot.

 

[awoohaw]

Happy to help. Best wishes!