Change value in an adjacent cell

siddharth_kale

New Member
Joined
Dec 29, 2010
Messages
24
Hello,

I would like to change the value in a cell to blank if the adjacent cell has value as rejected. Approved and Rejected are drop down list entries. This has to be done for a range of columns.

Any help is appreciated.

A B
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=64>1 v9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64> Approved</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"> Rejected</TD></TR></TBODY></TABLE>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

siddharth_kale

New Member
Joined
Dec 29, 2010
Messages
24
One more thing:

If the adjacent value is approved then the cell value should remain intact. If the adjacent value is rejected then the cell value should change to blank.
 

Lukey

New Member
Joined
Mar 24, 2011
Messages
9
what are the actual columns, in other words which column has the drop down lists and which column are you wanting empty dependent on the the condition in the drop down list cell
 

siddharth_kale

New Member
Joined
Dec 29, 2010
Messages
24
Hello,

Thanks for replying.

I am looking to make A2 as blank if someone has entered anything if the status is rejected.

If column B contains Approved then A1 should remain intact.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>v9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Approved</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Rejected</TD></TR></TBODY></TABLE>
 

Lukey

New Member
Joined
Mar 24, 2011
Messages
9

ADVERTISEMENT

Rather that having the return value in column a have it in column c and use the following formula:

Code:
=IF(B1="Approved",A1,IF(B1="Rejected","",""))

You can now hide column A and you get your required result.

Or you can change the column letters in the formula as needed.
 

siddharth_kale

New Member
Joined
Dec 29, 2010
Messages
24
Hello,

Thanks for replying.

this works but values in A1 will be entered by users. So if in the adjacent column B1 the status is approved, the entered value stays.

Now if the value entered in A2 is rejected, then A2 should become blank.
 

Lukey

New Member
Joined
Mar 24, 2011
Messages
9

ADVERTISEMENT

do you need the cell empty or just nothing showing?
 

Lukey

New Member
Joined
Mar 24, 2011
Messages
9
The problem you have is that if you clear the contents of the cell you will also clear any formulas. The only way i can think of doing it is writing a little bit of VBA that loops through a given range and does the deleting.
 

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Seems to me if user is going to enter values in cells to be cleared, this can't be done with a formula in that cell. Most likely will need to handle through VBA code in Worksheet_Change.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,982
Messages
5,526,054
Members
409,681
Latest member
marnu70

This Week's Hot Topics

Top