SOLVED
Home

Hiding duplicate formula results in a locked worksheet.

%3CLINGO-SUB%20id%3D%22lingo-sub-613878%22%20slang%3D%22en-US%22%3EHiding%20duplicate%20formula%20results%20in%20a%20locked%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-613878%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20worksheet%20with%206%20columns.%20(see%20attached%20file%20example)%3C%2FP%3E%3CP%3EI%20have%20protected%20the%20worksheet%20so%20that%20the%20column%20headed%20balance%20cannot%20be%20changed%20as%20this%20is%20where%20columns%20Amount%20Paid%20In%20and%20Amount%20Paid%20Out%20show%20via%20a%20formula%20into%20the%20Balance%20column%20but%20the%20Balance%20column%20needs%20the%20formula%20copied%20all%20the%20way%20down%20which%20shows%20the%20last%20Balance%20value%20all%20the%20way%20down%20the%20column.%3C%2FP%3E%3CP%3EHow%20can%20I%20still%20have%20the%20formula%20copied%20down%20the%20column%20but%20not%20have%20the%20last%20balance%20value%20copied%20down%20the%20sheet%20when%20there%20are%20no%20values%20in%20Amount%20Paid%20in%20and%20Amount%20Paid%20Out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-613878%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-613899%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20duplicate%20formula%20results%20in%20a%20locked%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-613899%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20to%20update%20the%20formula%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(B3%3D%22%22%2CC3%3D%22%22)%2C%22%22%2CSUM(E2-C3%2BB3))%3C%2FPRE%3E%3CP%3EUsing%20the%20functions%20IF%20and%20AND%2C%20you%20can%20build%20a%20logic%20to%20turn%20off%20the%20formula%20when%20the%20cells%20B3%20and%20C3%20are%20empty!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344584%22%20target%3D%22_blank%22%3E%40iananddaph%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-613917%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20duplicate%20formula%20results%20in%20a%20locked%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-613917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20very%20quick%20reply%20and%20a%20perfect%20outcome%2C%26nbsp%3B%3C%2FP%3E%3CP%3Emany%20thanks%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E
iananddaph
New Contributor

Hi,

I have a worksheet with 6 columns. (see attached file example)

I have protected the worksheet so that the column headed balance cannot be changed as this is where columns Amount Paid In and Amount Paid Out show via a formula into the Balance column but the Balance column needs the formula copied all the way down which shows the last Balance value all the way down the column.

How can I still have the formula copied down the column but not have the last balance value copied down the sheet when there are no values in Amount Paid in and Amount Paid Out.

 

Many thanks

Ian

2 Replies

Hi Ian,

 

Try to update the formula as follows:

=IF(AND(B3="",C3=""),"",SUM(E2-C3+B3))

Using the functions IF and AND, you can build a logic to turn off the formula when the cells B3 and C3 are empty!

 

Hope that helps

 

@iananddaph

Solution

@Haytham Amairah 

 

A very quick reply and a perfect outcome, 

many thanks

Ian

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies