Home

Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-881622%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881622%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20in%20cell%20M2%20to%20update%20that%20same%20cell%20ONLY%20if%20the%20current%20value%20is%20NO.%20Right%20now%2C%20there%20are%202%20possible%20values%20for%20M2%20-%20YES%20and%20NO.%20If%20the%20value%20is%20YES%2C%20I%20don't%20want%20the%20formula%20to%20execute.%20The%20current%20formula%20in%20M2%20is%20the%20following%3A%3CBR%20%2F%3E%3DIFNA(VLOOKUP(N2%2CClient_Tested%5B%5BLookup%5D%3A%5BTested%5D%5D%2C2%2CFALSE)%2C%22NO%22)%3CBR%20%2F%3EThe%20problem%20is%20that%20the%20values%20in%20the%20table%20Client_Tested%20only%20back%2035%20days.%20If%20something%20was%20tested%2040%20days%20ago%20(as%20an%20example)%2C%20this%20formula%20would%20mark%20that%20row%20as%20being%20not%20tested%2C%20when%20that's%20not%20true.%20I%20have%20played%20around%20with%20the%20following%20formula%3A%3CBR%20%2F%3E%3DIF(M2%3D%22YES%22%2C%22YES%22%2CIFNA(VLOOKUP(N2%2CClient_Tested%5B%5BLookup%5D%3A%5BTested%5D%5D%2C2%2CFALSE)%2C%22NO%22))%3CBR%20%2F%3EThis%20formula%20does%20not%20like%20that%20I'm%20running%20a%20formula%20against%20the%20same%20cell%20with%20the%20formula.%20Can%20you%20help%3F%20Ideally%2C%20I%20would%20like%20not%20to%20create%20a%20new%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-881622%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881882%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417100%22%20target%3D%22_blank%22%3E%40Diane365%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20circular%20referencing%20issue.%20I%20do%20not%20think%20there%20is%20a%20solution%20for%20this%20as%20this%20value%20is%20coming%20from%20the%20cell%20itself.%20You%20can%20write%20a%20formula%20to%20evaluate%20the%20%22YES%22%20situation%20in%20the%20cell%20however.%20Like%20IF(some%20condition%3B%22YES%22%3Banother%20condition)%26nbsp%3B%3C%2FP%3E%3CP%3E%C4%B0f%20the%20user%20would%20select%20the%20%22YES%22%20option%20than%20this%20is%20not%20possible%20due%20the%20cell%20content%20will%20also%26nbsp%3Bbe%20changed.%20When%20the%26nbsp%3B%20user%20changes%20the%20selection%20the%20old%20contents%20will%20be%20overwritten.%3C%2FP%3E%3CP%3EYou%20can%20try%20to%20check%20the%20%22Enable%20iterative%20calculation%22%20check%20box%20in%20File--%26gt%3BOptions--%26gt%3BFormula%20however%20even%20this%20can%20not%20do%20much%20as%20the%20cell%20value%20is%20dependent%20to%20user%20selection.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Diane365
Occasional Visitor

I need a formula in cell M2 to update that same cell ONLY if the current value is NO. Right now, there are 2 possible values for M2 - YES and NO. If the value is YES, I don't want the formula to execute. The current formula in M2 is the following:
=IFNA(VLOOKUP(N2,Client_Tested[[Lookup]:[Tested]],2,FALSE),"NO")
The problem is that the values in the table Client_Tested only back 35 days. If something was tested 40 days ago (as an example), this formula would mark that row as being not tested, when that's not true. I have played around with the following formula:
=IF(M2="YES","YES",IFNA(VLOOKUP(N2,Client_Tested[[Lookup]:[Tested]],2,FALSE),"NO"))
This formula does not like that I'm running a formula against the same cell with the formula. Can you help? Ideally, I would like not to create a new column.

1 Reply

@Diane365 

This is a circular referencing issue. I do not think there is a solution for this as this value is coming from the cell itself. You can write a formula to evaluate the "YES" situation in the cell however. Like IF(some condition;"YES";another condition) 

İf the user would select the "YES" option than this is not possible due the cell content will also be changed. When the  user changes the selection the old contents will be overwritten.

You can try to check the "Enable iterative calculation" check box in File-->Options-->Formula however even this can not do much as the cell value is dependent to user selection.

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