Forum Discussion
Cell that auto fills by if statement or allows manual input
I'm trying to have a cell "C5" that auto fills it using a if statement =IF(B5="No","N/A",""). Ideally i want when cell B5 contains "No" the cell auto fills. When the Cell B5 contains "Yes" it will allow manual input. Currently this works however obviously when you Enter anything into cell C5, it over rights the if statement. Is it possible to set it up as a rule so that it auto fills and allows manual input without being over written.
Link to Example:https://ctech-my.sharepoint.com/:x:/g/personal/matthewm_c-techservices_com_au/ESLsAtYnbA1IhLb32Xi1wngBVq1P5ny7b89kx_EGX7NIEA?e=4Sq9xc
Windows: Windows 10
Excel version: (Version 2204 Build 16.0.15128.20210) 64-bit
The functional programming style of an Excel sheet formula is intended to prevent the reassignment of a new value to a cell such as you describe. On the other hand. an imperative programming language such as VBA is allowed to make such state changes. Hence HansVogelaar 's solution.
There is a trick that may achieve the effect you require without resorting to VBA. If you insert a new column D then a formula there can be made to display in column B, provided the cell in column B is blank. The default text in column D/C is heavily indented and right aligned in order to move it left into the adjacent cell.
5 Replies
The functional programming style of an Excel sheet formula is intended to prevent the reassignment of a new value to a cell such as you describe. On the other hand. an imperative programming language such as VBA is allowed to make such state changes. Hence HansVogelaar 's solution.
There is a trick that may achieve the effect you require without resorting to VBA. If you insert a new column D then a formula there can be made to display in column B, provided the cell in column B is blank. The default text in column D/C is heavily indented and right aligned in order to move it left into the adjacent cell.
- Peter could you explain to me how you pushed the output of the formula in D to column C. I am trying to do the same thing
- Thanks for the help, it's not quite as good as the one above, however works far better for me given i will be sharing this online and (to my knowledge) cant use VBA on shared files on one drive. Thanks Heaps. Keep up the great work.
This requires a bit of VBA code. See the attached workbook, now a macro-enabled workbook (.xlsm).
You'll have to allow macros when you open it.
To view the code, right-click the sheet tab and select View Code from the context menu.
- Thanks for your response, it works exactly how i intended, though a non VBA option allows more flexibility when i have to share over one-drive. Keep up the great work.
