IF Statement Help

Copper Contributor

I have this formula and need to change "BU2" cell to every six cell going forward.
Example Month 1
=IF(COUNTBLANK('Prelims & Lien Waivers Revised'!BU2),"Missing Monthly Paper Work","Payment Released")
Example Month 2
=IF(COUNTBLANK('Prelims & Lien Waivers Revised'!CA2),"Missing Monthly Paper Work","Payment Released")

Is there a way I can make this formula to capture every sixth cell instead of changing this manually?

3 Replies

@Chen_W_hsu 

Maybe this...

 

If you are copying the formula across the columns...

=IF(COUNTBLANK(OFFSET('Prelims & Lien Waivers Revised'!$BU2,,(COLUMNS($BU2:BU2)-1)*6)),"Missing Monthly Paper Work","Payment Released")

 

If you are copying the formula down the rows...

=IF(COUNTBLANK(OFFSET('Prelims & Lien Waivers Revised'!BU$2,,(ROWS(BU$2:BU2)-1)*6)),"Missing Monthly Paper Work","Payment Released")

@Subodh_Tiwari_sktneer 

 

What happens if I am using this formula on a worksheets in the workbook looking at another worksheets?

 

Example: Payment Status worksheet looking at cell on Prelims & Lien Waiver worksheet

 

The only thing change is the cell on the Prelims & Lien Waiver worksheet and not Payment Status worksheet

 

Thank You for your help

 

Chen 

@Chen_W_hsu 

 

It doesn't matter on which sheet you use that formula, the formula will always refer to the cells in the 'Prelims & Lien Waivers Revised' Sheet

 

Assuming you are copying down that formula down the rows...

So in the first formula cell, the formula will refer to the cell 'Prelims & Lien Waivers Revised'!$BU$2

and the next cell down the row, it will refer to the cell 'Prelims & Lien Waivers Revised'!$CA$2

and so on so forth.