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]

im confused. with your write up. you can't have a value and a formula in cell S6.

 

[Sumanmathew]

im confused. with your write up. you can't have a value and a formula in cell S6.

no Sir, there is no value there(in cells S6 to X6). Only formula. I wrote the values to make it clear as to which values should appear there in those cells.

 

[Sumanmathew]

Let me make it more clear,
In column S7 to S14, there are blank and non blank cells.
the non blank cells are S7,S9,S11,S13,S14.

the corresponsing values for these non blank cells in columns O are
20A,32A,32A,400A,1600A.

The maximum is 1600A .
I want the formula to display that maximum in cell S6.


same with other cells too (T6 to X6)

 

[awoohaw]

no Sir, there is no value there(in cells S6 to X6). Only formula. I wrote the values to make it clear as to which values should appear there in those cells.

you wrote this: "wherever there are non-blank cells in column S"

 

[awoohaw]

Do you want the column maximum of the cells below row 6?

 

[awoohaw]

For column T how do you calculate the 600 is the maximum?

 

[awoohaw]

Are you aksing for Largest, 2nd Largest, and 3rd Largest?

 

[Sumanmathew]

Do you want the column maximum of the cells below row 6?

For column T how do you calculate the 600 is the maximum?

In Column T , the non blank cells are T8,T10,T11,T13.

The corresponding values in Column O are 600A, 100A,32A,400A

out of this 600 is the highest.

 

[Sumanmathew]

Are you aksing for Largest, 2nd Largest, and 3rd Largest?

Only the largest of column O. But depending upon the non blank cells in S column.