Aug 27 2019 11:35 AM
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?
Aug 27 2019 11:52 AM
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")
Aug 27 2019 12:09 PM
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
Aug 27 2019 12:19 PM
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.