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
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies