My IF formulas wont work

gritter27

New Member
Joined
Oct 30, 2009
Messages
6
Here is what I would like to do:

Anytime there is a 1 in column B, I would like for columns G-I and columns L-N to display "N/A". I have tried all kinds of formulas, none of which will work. Is there any way to do this? Please help!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi and welcome to the Board!!!

Do you want a formula solution of a code solution? How is column "B" populated?

lenze
 
Upvote 0
Hello and welcome to MrExcel.


Try

<b>Sheet7</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:25px;" /><col style="width:25px;" /><col style="width:25px;" /><col style="width:25px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">G</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">H</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">I</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">J</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">K</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">L</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">M</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">N</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td >#N/A</td><td >#N/A</td><td >#N/A</td><td > </td><td > </td><td >#N/A</td><td >#N/A</td><td >#N/A</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >x</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td >#N/A</td><td >#N/A</td><td >#N/A</td><td > </td><td > </td><td >#N/A</td><td >#N/A</td><td >#N/A</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G3</td><td >=IF($B3=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >H3</td><td >=IF($B3=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >I3</td><td >=IF($B3=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >L3</td><td >=IF($B3=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >M3</td><td >=IF($B3=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >N3</td><td >=IF($B3=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >G4</td><td >=IF($B4=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >H4</td><td >=IF($B4=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >I4</td><td >=IF($B4=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >L4</td><td >=IF($B4=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >M4</td><td >=IF($B4=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >N4</td><td >=IF($B4=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >G5</td><td >=IF($B5=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >H5</td><td >=IF($B5=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >I5</td><td >=IF($B5=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >L5</td><td >=IF($B5=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >M5</td><td >=IF($B5=1,NA<span style=' color:008000; '>()</span>,"")</td></tr><tr><td >N5</td><td >=IF($B5=1,NA<span style=' color:008000; '>()</span>,"")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
Wow! thank you for such quick responses! Lenze, my B column is unpopulated right now. :) I just want to make it easier for myself to see which cells need to be checked off. I am making a sort of check sheet for documents that need to be turned in for certain clients. Some require the documents in those columns (G-I and L-N), and some don't. So, any way that you can see to help would be great.

VoG, I think that would work, but do I have to imput it in every single cell and change the row on the B column for each row? My table consists of about 200 rows.

Thank you so much!
 
Upvote 0
Wow! thank you for such quick responses! Lenze, my B column is unpopulated right now. :) I just want to make it easier for myself to see which cells need to be checked off. I am making a sort of check sheet for documents that need to be turned in for certain clients. Some require the documents in those columns (G-I and L-N), and some don't. So, any way that you can see to help would be great.

VoG, I think that would work, but do I have to imput it in every single cell and change the row on the B column for each row? My table consists of about 200 rows and grows each time we add a client.

Thank you so much!
 
Upvote 0
Oh also, I have this formula:

=MOD(ROW(),2)

on the table to highlight every other row. Could that be affecting the other formula?
 
Upvote 0
You can autofill formulas.

Enter the formula in G3 (for example). Then click in G3 and hover the mouse over the bottom right of the cell until the cursor turns into a +. Then hold down the left mouse button and drag across to I3 then release the mouse.

Now select G3:I3, hover the mouse over the bottom right of the cell until the cursor turns into a +. Then hold down the left mouse button and drag down as far as needed. The way the formula is written it won't display anything for rows where column B is blank.
 
Upvote 0
For Code, you could use
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Target = 1 Then
    Cells(Target.Row, "G").Resize(1, 3) = "#NA"
    Cells(Target.Row, "L").Resize(1, 3) = "#NA"
Else
    Cells(Target.Row, "G").Resize(1, 3) = ""
    Cells(Target.Row, "L").Resize(1, 3) = ""
End If
End Sub
In the WorkSheet module (Right Click the sheet tab and choose "View Code")
lenze
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top