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
Highlighted
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

@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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies