Excel 2016：如何设置单元格可编辑输入文本？含下拉关联公式问题

Hi there! Let’s tackle your Excel questions step by step—since you’re still getting familiar with 2016, I’ll keep things straightforward.

1. Completing & Cleaning Up Your B19 Formula #

Your current nested IF formula cuts off halfway, so it won’t work as intended. Plus, nested IFs get messy quickly—luckily, Excel 2016 has the IFS function that simplifies this a lot. I also spotted a tiny typo: "Account Maintainance" should be "Account Maintenance" (missing an 'n').

Here’s the optimized, complete version using IFS:

=IFS(
    B18="International Incoming MT – Business ", "Money Transfers",
    B18="International Outgoing MT – Business ", "Money Transfers",
    B18="Annual Account Maintenance Fee – Business ", "Account Maintenance",
    B18="GPRS POS Fee ", "POS",
    B18="E-Banking Maintenance Fee ", "Business Services",
    TRUE, "Other" // Catch-all for any unlisted B18 options
)

• The TRUE line acts as a default: if B18 doesn’t match any of your listed options, it’ll return "Other" (you can tweak this to whatever makes sense for your sheet).
• If you’d rather stick with nested IFs (though IFS is easier to read), here’s the finished nested version:

=IF(B18="International Incoming MT – Business ","Money Transfers",IF(B18="International Outgoing MT – Business ","Money Transfers",IF(B18="Annual Account Maintenance Fee – Business ","Account Maintenance",IF(B18="GPRS POS Fee ","POS",IF(B18="E-Banking Maintenance Fee ","Business Services","Other")))))

2. Making Cells Editable & Allowing Text Input #

How to do this depends on whether the cell is protected or has a dropdown list (like B18):

For Regular Cells (No Dropdown) #

If a cell is locked and the sheet is protected, you can’t edit it. Fix this:

• Select the cell(s) you want to edit (e.g., B19)
• Right-click → Format Cells → Switch to the Protection tab
• Uncheck the Locked box (all cells are locked by default, but this only matters if the sheet is protected)
• If your sheet is protected: Go to the Review tab → Click Unprotect Sheet (enter a password if one was set)

For Cells with Dropdown Lists (Like B18) #

By default, dropdowns might block text that’s not in the list. To allow custom text input:

• Select the cell (B18)
• Go to the Data tab → Click Data Validation
• Switch to the Error Alert tab
• Change the Style from "Stop" to "Warning" (gives a heads-up but lets you proceed) or "Information" (just a message), or uncheck Show error alert after invalid data is entered entirely
• Click OK—now you can type custom text into the cell, even if it’s not in the dropdown options

内容的提问来源于stack exchange，提问作者dgj