IF Statement quandary

[Bazzza67]

Hi Boardmembers

I am hoping someone can guide me.

I have an IF statement that looks to another reference worksheet tab in the same workbook. I want to drag the IF statement down, but instead of looking at the next concurrent reference cell, I want to look at every 6th cell in the reference worksheet.

Can this be achieved please?

Here is an example of the formula I have....

=IF(OR('Current and Pending Projects'!A222="",'Current and Pending Projects'!A222="Enter Client Name Here"),"",'Current and Pending Projects'!A222)

When I drag down, I want the formula to look at A228, then next line A234, then A240 etc etc

Thank you in advance

 

[Joe4]

Here is an example of the formula I have....

=IF(OR('Current and Pending Projects'!A222="",'Current and Pending Projects'!A222="Enter Client Name Here"),"",'Current and Pending Projects'!A222)

We can create a formula that will do that, but it is important to know exactly what cell this initial formula is going in, so we can calculate the next row number based on the row number the formula is being placed in.

 

[Bazzza67]

Hi
Have attached an image

 

[Joe4]

Hi
Have attached an image

That really doesn't answer my question.
I want to know what the exact cell address of the cell that you are putting this formula in:

=IF(OR('Current and Pending Projects'!A222="",'Current and Pending Projects'!A222="Enter Client Name Here"),"",'Current and Pending Projects'!A222)

That is all I need to know.

 

[Bazzza67]

Ah, apologies

The cell range is (C2:C20)

 

[Joe4]

OK, place this formula in cell C2 and copy down to cell C20:

=IF(OR(INDIRECT("'Current and Pending Projects'!A"&((ROW()-2)*6+222))="",INDIRECT("'Current and Pending Projects'!A"&((ROW()-2)*6+222))="Enter Client Name Here"),"",INDIRECT("'Current and Pending Projects'!A"&((ROW()-2)*6+222)))