Home

IF Statement Help

%3CLINGO-SUB%20id%3D%22lingo-sub-825339%22%20slang%3D%22en-US%22%3EIF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825339%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20this%20formula%20and%20need%20to%20change%20%22BU2%22%20cell%20to%20every%20six%20cell%20going%20forward.%3CBR%20%2F%3EExample%20Month%201%3CBR%20%2F%3E%3DIF(COUNTBLANK('Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'!BU2)%2C%22Missing%20Monthly%20Paper%20Work%22%2C%22Payment%20Released%22)%3CBR%20%2F%3EExample%20Month%202%3CBR%20%2F%3E%3DIF(COUNTBLANK('Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'!CA2)%2C%22Missing%20Monthly%20Paper%20Work%22%2C%22Payment%20Released%22)%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20I%20can%20make%20this%20formula%20to%20capture%20every%20sixth%20cell%20instead%20of%20changing%20this%20manually%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-825339%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825381%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399185%22%20target%3D%22_blank%22%3E%40Chen_W_hsu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20copying%20the%20formula%20across%20the%20columns...%3C%2FP%3E%3CP%3E%3DIF(COUNTBLANK(OFFSET('Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'!%24BU2%2C%2C(COLUMNS(%24BU2%3ABU2)-1)*6))%2C%22Missing%20Monthly%20Paper%20Work%22%2C%22Payment%20Released%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20copying%20the%20formula%20down%20the%20rows...%3C%2FP%3E%3CP%3E%3DIF(COUNTBLANK(OFFSET('Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'!BU%242%2C%2C(ROWS(BU%242%3ABU2)-1)*6))%2C%22Missing%20Monthly%20Paper%20Work%22%2C%22Payment%20Released%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825402%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20happens%20if%20I%20am%20using%20this%20formula%20on%20a%20worksheets%20in%20the%20workbook%20looking%20at%20another%20worksheets%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20Payment%20Status%20worksheet%20looking%20at%20cell%20on%20Prelims%20%26amp%3B%20Lien%20Waiver%20worksheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20thing%20change%20is%20the%20cell%20on%20the%26nbsp%3BPrelims%20%26amp%3B%20Lien%20Waiver%20worksheet%20and%20not%26nbsp%3BPayment%20Status%20worksheet%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EThank%20You%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChen%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-825417%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-825417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399185%22%20target%3D%22_blank%22%3E%40Chen_W_hsu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20doesn't%20matter%20on%20which%20sheet%20you%20use%20that%20formula%2C%20the%20formula%20will%20always%20refer%20to%20the%20cells%20in%20the%20'Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'%20Sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20you%20are%20copying%20down%20that%20formula%20down%20the%20rows...%3C%2FP%3E%3CP%3ESo%20in%20the%20first%20formula%20cell%2C%20the%20formula%20will%20refer%20to%20the%20cell%20'Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'!%3CSTRONG%3E%24BU%242%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20the%20next%20cell%20down%20the%20row%2C%20it%20will%20refer%20to%20the%20cell%20'Prelims%20%26amp%3B%20Lien%20Waivers%20Revised'!%3CSTRONG%3E%24CA%242%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20so%20on%20so%20forth.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chen_W_hsu
New 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
Highlighted

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies