Forum Discussion
Formula #NA ERROR
Hi All, I have a small issue with a formula error, hoping someone can help. I HAVE A FORMULA WHICH RELIES ON TWO CELLS HAVING A VALUE EQUAL OR MORE THAN 1. I’VE USED THE FORMULA BELOW WHICH WOR...
Hi again, one more question… is there a way to allow the cell with this formula to be adjusted manually without the formula disappearing?
Example: cell A2 has this formula
=if(and(y2>=1,$ag$2>=1),1,"")
But if I want to manually delete the result to produce a blank cell (whilst the values are true in y2&ag2)…… it clears the formula also.
What I’d like to happen, is if I delete the result in cell A2, it will reinstate once I change the value again in either y2 or AG2
Hope it’s possible
yes and no. I don't understand exactly what you want or why (which if we know we might be able to better supply an alternative or workaround or just better solution), but you can most likely do what you want using a macro/VBA. Using just cell formulas, you can't have a formula get deleted and magically reappear on its own. That said, there are usually ways to achieve what you want using formulas and/or helper columns.
Hi, thanks for looking at this. I have attached visual to explain. I hope it makes sense.
Many thanks
- yeah what you are asking for is really a macro. You could add in parallel A-D columns that are "force on/off" an then basically have the formula incorporate that corresponding cell also. in terms of numbers you could have those cells be -1 (force off),0 (nothing),1 (force on) and add it to the result of the checks and then compare as >0
