Excel IF Statments or VBA

Question

I have a sheet that in Column M it has Date Visited and then I have put in a formula that then adds 6 months to the date which is displayed in Column N.

Then I have a hidden two columns that works out the date and and from that date it has been RAG assessed, Red Amber Green.

Column M is Date Visited
Column N is Next Visited
Column Q is the formula that tells you how many days over or under
Column R is where based on Column Q if it is G, A , R (Green, Amber or Red)

I have this formula which list all the dates and removes any blank cells in column N

=IFERROR(INDEX(Sheet2!$N$3:$N$78, SMALL(IF(LEN(Sheet2!$N$3:$N$78)=0,"", ROW(Sheet2!$N$3:$N$78)-MIN(ROW(Sheet2!$N$3:$N$78))+1), ROW(Sheet2!N2))),"")

What I want is when the below formula is true then the above to be triggered but only for cells that have R in Column R

=IF (CNI!R3="R",SHEET2!N3,"")

Hope this makes sense.

I don't mind using VBA if it is easier to achieve

In trying to understand, I'll repeat what I think I'm reading. Please correct where incorrect. You have items that can be handled by If/Then and conditional formatting in your workbook. You are also looking to remove/delete lines if they are missing data (based on column N). Regarding the If/Then, you can nest the if statements, where if sheets CNI cell R3 = "R" then your true statement would be the IFERROR...))),"") with your false statement being "". VBA can accept that code (noting that every " needs to be "") if you're looking to delete lines. This would be similar in VBA or excel. — Cyril
@pnuts Sheet1 is where is where the formula sits and sheet 2 is where all the data is. — Php Newbie
@Cyril when I try to nest the if statements it just returns true or false and the blank cells appear again. — Php Newbie

Cyril Cyril · Accepted Answer · 2017-06-29T13:31:43

Sorry, I'm a couple days behind on responding to you for this.

=IFERROR(INDEX(Sheet2!$N$3:$N$78, SMALL(_
IF(LEN(Sheet2!$N$3:$N$78)=0,"", ROW(Sheet2!$N$3:$N$78)-MIN(ROW(Sheet2!$N$3:$N$78)_
)+1), ROW(Sheet2!N2))),"")

But you want the this to happen if you have an additional criteria, this:

=IF (CNI!R3="R",SHEET2!N3,"")

You can have multiple criteria in your if statement using AND(). I am thinking that you could use the following, presuming you only want that specific If statement to apply (taken from the above, broken out If statement in the first coding section of my post):

IF(AND(CNI!R3="R",LEN(Sheet2!N$3:$N$78)=0),"", ROW(Sheet2!$N$3:$N$78)-MIN(ROW(Sheet2!$N$3:$N$78)

If your goal is to replace (from your second bit of code) the Sheet2!N3 (your true condition), then you would look at the following line, which includes both lines of your code:

=IF (CNI!R3="R",IFERROR(INDEX(Sheet2!$N$3:$N$78, SMALL(IF(LEN(Sheet2!$N$3:$N$78)=0,"", ROW(Sheet2!$N$3:$N$78)-MIN(ROW(Sheet2!$N$3:$N$78))+1), ROW(Sheet2!N2))),""),"")

Let me know if that helps clear up the issue!

Excel IF Statments or VBA

1 Answers