Forum Discussion

Jupiter1's avatar
Jupiter1
Copper Contributor
May 07, 2020

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...
excel
mtarler's avatar
mtarler
Silver Contributor
May 07, 2020
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.
Jupiter1's avatar
Jupiter1
Copper Contributor
May 07, 2020

mtarler 

 

Hi, thanks for looking at this. I have attached visual to explain. I hope it makes sense.

 

Many thanks

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 07, 2020
    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
    • Jupiter1's avatar
      Jupiter1
      Copper Contributor
      May 07, 2020

      mtarler 

      Thank you so much for your help! That’s great news! Unfortunately, not too sure if I totally understand how to go about this. I’m unfamiliar with macros. Would you be able to show me? I’ve attached document if it helps.

       

      Many thanks