Make a formula return values only once when a condition is met.

[alphahotel]

Hi everyone,

Is it possible to configure an IF formula that if it has ONCE met the preferred condition as in say date in A1 = date in A2 then the formula to run ONLY ONCE, and if not then leave the cell empty? What I want to do is to have excel run the formula in a cell that meets two conditions: A) cell is empty and B) meets date A1 = date A2 rule. OK, this is easy enough. However,

Our fixed date values are in A2....An. But, date value in A1 is changed via a filter (i.e slicer). Also our formulas are in B2....XY2, B3....XY3... B5....XY5, so on and so forth. Assume A1 states May 1, and A2 also states May 1. What I am after is twofold: A) If B2..Bn row is empty and A1=A2 then go fetch me this value from Table X. B) However, if the cell is already full (assume now that we have changed the A1 filter from May 1 to May 2 [A1=A3] ), then do nothing for all other rows except B3...Bn, along with the caveat that it also doesn't change the values previously fetched for B2...Bn. So if the formula checks the cell is already full (or in essence has been filled once) it returns with I should not run me (again).

In layman terms, "Is there any way to embed the result of a formula evaluation in a cell, such that the evaluation only happens once?"

Many thanks.

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MarvinP]

Hi alphahotel,

It sounds like you really need a filter instead of a formula. In imagining a formula that might do this leads me to a circular ref in that cell.