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
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
Help with minutes/seconds to seconds.
SteveMorley in Excel on
1 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies