My IF formulas wont work

[gritter27]

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!

 

[lenze]

Hi and welcome to the Board!!!

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

lenze

 

[VoG]

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-styleutset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">G</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">H</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">I</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">J</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">K</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">L</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">M</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">N</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;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-styleutset;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-styleutset;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

 

[gritter27]

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!

 

[gritter27]

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!

 

[gritter27]

Oh also, I have this formula:

=MOD(ROW(),2)

on the table to highlight every other row. Could that be affecting the other formula?

 

[VoG]

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.

 

[gritter27]

Thank you thank you! You have helped me so much!

 

[lenze]

For Code, you could use

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

 

[gritter27]

thank you both, you guys are great!