Get value from the adjacent cell [Solved/Closed]

Report
-
 Ninz -
Hello,

I have been working on excel worksheet where I need to get the cell value (in this case it is the date) from the adjacent column as well as highlight/background colour of the maximum value in column B. The max value changes based on the input in column.

For e.g

A B
1-Jun 10
2-Jun 12
3-Jun 18
4-Jun 13

Look fwd to some solutions

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
Could you please upload a sample file with sample data, CONDITIONAL FORMAT , MACRO, formula etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Hi,

Thks for ur response.....i hv mentioned below the problem in more detail....

DATE DAY Apple Orange Grape
14/6 Mon 1 6 3
15/6 Tue 3 9 4
16/6 Wed 4 2 5
17/6 Thu 5 8 6
18/6 Fri 8 6 9
21/6 Mon 5 3 8


I'm trying to create a excel worksheet that highlights by changing the background colour of the maximum value in the apple, Orange & Grape column . The max value changes based on the input in another column.

I had come across this formula =MAX(IF(NOT(ISNA(C2:C7)),C2:C7)) but I dont seem to be getting the desired answer.

Secondly, I need to get the date that corresponds with the maximum value that is located in the column to the left.

Date Price
High 8 =MAX(C2:C7)
Low 1 =MIN(C2:C7)


I was trying VLOOKUP but again not getting the desired answer.

I look forward to your reply...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
I am not clear what you want. At one hand you say that you want max value of apple from the table that you listed and on the other hand you say maximum value comes from some other place.

Could you add an EXCEL file at any share site and post back the link, Please show in that book how the data is NOW AND how would you like that the final product to look like.
Sorry if I was not able to explain my problem. I have uploaded the sample file for your reference and have mentioned below my problems.

https://authentification.site/files/23162922/rate_ref.xls

Look forward to your solutions
Thank you so much ....it worked!!!
hI,

Thks for ur response...pls find below a more detailed description of my problem...

DATE DAY APPLE GRAPE ORANGE
14/6 Mon 46.57 56.81 68.23
15/6 Tue 46.73 56.92 68.67
16/6 Wed 46.35 57.16 68.67
17/6 Thu 46.50 57.09 68.36
18/6 Fri 46.14 57.18 68.46
21/6 Mon 45.64 56.83 68.06


i need to highlight/background colour of the maximum value in APPLE column and other columns respectively

Date Price
High 15/6 46.31 =Max(C2:C7)
Low 21/7 45.64 =Min(C2:C7)

maximum value in the apple column is 46.73 so I need to get the date which is 15/6. The max value changes based on the input in column.


Await ur reply...
Hi,

Thks for ur response.....i hv mentioned below the problem in more detail....

DATE---DAY----Apple----Orange----Grape
14/6 Mon 1 6 3
15/6 Tue 3 9 4
16/6 Wed 4 2 5
17/6 Thu 5 8 6
18/6 Fri 8 6 9
21/6 Mon 5 3 8


I'm trying to create a excel worksheet that highlights by changing the background colour of the maximum value in the apple, Orange & Grape column . The max value changes based on the input in another column.

I had come across this formula =MAX(IF(NOT(ISNA(C2:C7)),C2:C7)) but I dont seem to be getting the desired answer.

Secondly, I need to get the date that corresponds with the maximum value that is located in the column to the left.

Date Price
High-------18/6--------8 =MAX(C2:C7)
Low--------14/6-------1 =MIN(C2:C7)


I was trying VLOOKUP but again not getting the desired answer.

I look forward to your reply...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
If all you want is to highlight in your table for each column the maximum value in that column, then you can use conditional format

based on your sample data
click on j5 and select from J5 till L20

now goto conditional formatting and enter this formula

=J5=MAX(j$5:J$20)
and then click on the format button of conditional formatting and chose the color


for date you can use this formula (this is for e41)
=INDIRECT("H" &MATCH(F41,J:J,0))

this is for j41
=INDIRECT("H" &MATCH(K41,K:k,0))

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!