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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies