SOLVED

IF Function both in formual and conditional formatting.

%3CLINGO-SUB%20id%3D%22lingo-sub-1478689%22%20slang%3D%22en-US%22%3EIF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478689%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20already%20enabled%20the%20IF%20function%20to%20correspond%20to%20certain%20values%20and%20return%20text.%20I%20have%20also%20conditionally%20formatted%20the%20text%20to%20colours.%20When%20I%20don't%20yet%20have%20data%20in%20the%20cells%20below%20and%20adjacent%20it%20returns%20a%20blank%20cell%20(IFERROR%20used).%20Now%20when%20I%20do%20this%20it%20assumes%20one%20of%20the%20IF%20function%20of%20%26lt%3B%20Cell%20Value%20and%20returns%20the%20text.%20However%20when%20the%20cell%20is%20blank%20(as%20I%20don't%20have%20data%20yet)%20I%20would%20like%20to%20add%20in%20formatting%20for%20the%20highlighted%20cells%20to%20also%20remain%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20it%20is%20incredibly%20hard%20to%20communicate%20this%20error%20over%20text%20but%20any%20help%20will%20be%20greatfully%20appreciated.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22GaryPepperell_2-1592666873300.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199878iD35CE1B1DC98D966%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22GaryPepperell_2-1592666873300.png%22%20alt%3D%22GaryPepperell_2-1592666873300.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGPepperell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1478689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478718%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705525%22%20target%3D%22_blank%22%3E%40GaryPepperell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGary%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20right%20-%20it%20is%20difficult%20to%20communicate.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20attach%20the%20document%20we%20can%20look%20and%20see!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478729%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705525%22%20target%3D%22_blank%22%3E%40GaryPepperell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20mean%20something%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20703px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199881i9DE632B23DC002E9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478805%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20response.%20I%20have%20added%20in%20the%20document.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478814%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478814%22%20slang%3D%22en-US%22%3E%3CP%3E-ERR%3AREF-NOT-FOUND-%40Sergei%20Baklan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20you%20so%20much!%20That%20works%20great.%20I%20knew%20I%20was%20missing%20just%20a%20little%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated%20and%20kind%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGPepperell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478815%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478815%22%20slang%3D%22en-US%22%3E%40peteryac6-%20all%20sorted%20now%2C%20thank%20you%20for%20looking%20into%20this%20for%20me%20though!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478888%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20both%20in%20formual%20and%20conditional%20formatting.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478888%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705525%22%20target%3D%22_blank%22%3E%40GaryPepperell%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, 

 

I have already enabled the IF function to correspond to certain values and return text. I have also conditionally formatted the text to colours. When I don't yet have data in the cells below and adjacent it returns a blank cell (IFERROR used). Now when I do this it assumes one of the IF function of < Cell Value and returns the text. However when the cell is blank (as I don't have data yet) I would like to add in formatting for the highlighted cells to also remain blank.

 

I understand it is incredibly hard to communicate this error over text but any help will be greatfully appreciated.

GaryPepperell_2-1592666873300.png

 

 

 

Many thanks,

 

GPepperell

6 Replies
Highlighted

@GaryPepperell 

 

Gary

 

You are right - it is difficult to communicate. 

 

If you can attach the document we can look and see!

 

thanks!

 

Peter

Highlighted
Best Response confirmed by GaryPepperell (New Contributor)
Solution

@GaryPepperell 

Perhaps you mean something like this

image.png

Highlighted

@peteryac60 

 

Thanks for your response. I have added in the document. 

Highlighted

@Sergei Baklan,

 

Thanks for you so much! That works great. I knew I was missing just a little something.

 

Much appreciated and kind regards,

 

GPepperell

Highlighted
@peteryac6- all sorted now, thank you for looking into this for me though!
Highlighted

@GaryPepperell , you are welcome, glad to help