Differentiating between a Formula and Hard Entered (Static) Data - Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2677710%22%20slang%3D%22en-US%22%3EDifferentiating%20between%20a%20Formula%20and%20Hard%20Entered%20(Static)%20Data%20-%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2677710%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20problem%20of%20trying%20to%20conditional%20format%20a%20cell%20based%20upon%20whether%20the%20contents%20of%20each%20cell%20are%20hard%20entered%20(static)%20data%20or%20if%20there%20is%20a%20formula%20in%20the%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20of%20this%20is%20when%20I%20am%20calculating%20dates%20and%20I%20want%20to%20actualise%20this%20information%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22471px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3EItem%20ID%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3ELeaving%20Date%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3EDuration%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3EArrival%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3E%26nbsp%3BItem%20A%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3E18-Oct-21%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3E01-Nov-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3E%26nbsp%3BItem%20B%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3E01-Aug-21%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3E23%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3E24-Aug-21%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20contents%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3EItem%20ID%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3ELeaving%20Date%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3EDuration%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3EArrival%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3E%26nbsp%3BItem%20A%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3E18-Oct-21%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3E%3DB2%2BB3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2278.6406px%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2278.7031px%22%20height%3D%2230px%22%3E%26nbsp%3BItem%20B%3C%2FTD%3E%3CTD%20width%3D%22112.562px%22%20height%3D%2230px%22%3E01-Aug-21%3C%2FTD%3E%3CTD%20width%3D%2272.7031px%22%20height%3D%2230px%22%3E23%3C%2FTD%3E%3CTD%20width%3D%22127.391px%22%20height%3D%2230px%22%3E24-Aug-21%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20cells%20B2%2C%20C2%20and%20C4%20to%20be%20highlighted%20by%20conditional%20formatting%20as%20they%20are%20hard%20entered%2C%20however%2C%20due%20to%20the%20fact%20B4%20is%20a%20formula%20calculated%20by%20leaving%20date%20%2B%20expected%20duration%20then%20it%20shouldn't%20be%20highlighted%20until%20actualised.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20further%20information%20required%20please%20advise%2C%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3EBailey%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2677710%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2678613%22%20slang%3D%22en-US%22%3ERe%3A%20Differentiating%20between%20a%20Formula%20and%20Hard%20Entered%20(Static)%20Data%20-%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2678613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1135817%22%20target%3D%22_blank%22%3E%40Bailey_Stiles_Epiroc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20understand%20what%20you%20want%20(and%20your%20tables%20have%20switched%20the%20row%20numbers%20and%20column%20letters)%2C%20but%20you%20could%20create%20rules%20of%20type%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%20In%20the%20formula%20for%20the%20rule%2C%20you%20can%20use%20the%20ISFORMULA%20function.%3C%2FP%3E%0A%3CP%3EISFORMULA(B2)%20will%20return%20TRUE%20if%20B2%20contains%20a%20formula%20and%20FALSE%20if%20it%20doesn't.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have the problem of trying to conditional format a cell based upon whether the contents of each cell are hard entered (static) data or if there is a formula in the cell. 

Example of this is when I am calculating dates and I want to actualise this information:

 

 1234
AItem IDLeaving DateDurationArrival Date
B Item A18-Oct-211401-Nov-21
C Item B01-Aug-212324-Aug-21

 

Cell contents:

 1234
AItem IDLeaving DateDurationArrival Date
B Item A18-Oct-2114=B2+B3
C Item B01-Aug-212324-Aug-21

 

I want cells B2, C2 and C4 to be highlighted by conditional formatting as they are hard entered, however, due to the fact B4 is a formula calculated by leaving date + expected duration then it shouldn't be highlighted until actualised.

 

Any further information required please advise, thanks.

 

Kind regards,

Bailey

 

1 Reply

@Bailey_Stiles_Epiroc 

I don't understand what you want (and your tables have switched the row numbers and column letters), but you could create rules of type 'Use a formula to determine which cells to format'. In the formula for the rule, you can use the ISFORMULA function.

ISFORMULA(B2) will return TRUE if B2 contains a formula and FALSE if it doesn't.