IF/THEN:Make multiple cells read N/A or remain fillable depending on 1 other cell dropdown

Hi,

I need to use the IF/THEN argument but NOT fill that argument into the cells I need to obey it.

See attached excel sheet with comment...it will be very clear what I'm asking.

I have a YES/NO drop down menu in E8. The question to be answered by the drop down: Was this analysis required?

If yes, I need all related cells to that analysis (non-sequential and highlighted on attached sheet) to remain blank and fillable for data entry. If "No" is selected from the drop down, I need all of those (non-sequential) highlighted cells to auto fill with N/A.

Can anyone help with this?

Thanks!

If I understand you correctly, you want (for instance) B14 to return NA depending on what is in E8, but you also what this cell to allow manual entry?

A cell can contain EITHER a formula OR data (manually entered), it cannot contain both. So if this will be a regularly used form, you cannot do what you want, with formulas, you will need VBA.

If this is a once off, you could indeed have your formula in B14 and allow the user to replace the formula with manually entered data - but then your formula is gone.

As Ford, said, you can't do this with formulas unless you maintain a template that is used fresh each time. Here is a VBA solution. The code in the worksheet module clears the contents of your designated cells if the cell E8 is cleared or "Yes." If "No" is selected, the designated cells are filled with "N/A"

Please Login or Register  to view this content.

Happy to help

Thanks for the rep

Okay, first I created a defined name. I held down the contrl key while I clicked on all your your cells that you want (possibly) NA in. Then with all these selected, I clicked in the Name box (the little white box at the top that usually has a cell ID like A1. I typed Fillable into that box and hit ENTER. Now those cells are called Fillable. If you click on the dropdown arrow next to the Name Box, you will be able to select "Fillable" and excel will automatically select those cells. You can also find the Defined Name Manager on the Formula Tab of your Ribbon.

From there, you would right click on the sheet tab and select "View Code." This opens up the VBA Editor to the sheet module. Then paste the code I wrote into the large white text area and close the VBA editor. Last step would be to save your workbook as an xlsm instead of an xlsx.

Hope that helps.