The next time you need to create a drop-down list and refer to another range, you can use this method. In the drop-down list, you can also see 7 items. For example, we add a new item into the original range in cell A8. The next time when you add or delete an item in the source range, the items in the list will automatically update. Thus, the drop-down list has been created in the cell.
And then click the button “OK” in the ribbon to save the setting. You can change certain elements in the formula according to the actual worksheet. And then input this formula into the “Source” text box:. In the new pop-up window, choose the “List” in the text box of “Allow”. After that, click the button “Data Validation” in the toolbar. And then click the tab “Data” in the ribbon. Here we will click a cell A2 in another worksheet. Click the target cell that you need to create the list. The image below is the source range in a worksheet. In this method, you can use the OFFSET function in the data validation. By using those methods, the drop-down list will refresh automatically. But now, we have found two effective methods for you. Whenever you add or delete an item in the range, you need to check the drop-down list in the target cell. When the source range changes, the drop-down list will also be affected. In our previous article How to Create Drop-down List from a Range of Cells in Your Excel, we have introduced the drop-down list in detail. In this article, we will introduce two methods to auto refresh the drop-down list. This will not work with data validation.The drop-down list in data validation is a frequently used feature in Excel. Check to make sure you do not have any macros. Not all features are available in Excel 2010. If you accidentally select multiple spreadsheets, this will cause an error. To inactivate, use the steps below.įrom the toolbar Ribbon Select: “Review” – “Unshare Workbook” Possible Reason 2: One Sheet SelectedĬheck to make sure you are only selecting one worksheet. 
If the file is actively being shared, this will be active.
This will allow you to reactivate the data validation tool. The other option is to copy the data set into a new worksheet that is not shared. This is to protect the linked data from producing an error.Īnnoying right! In order to fix this issue, make sure you unprotect your worksheet.
If you spreadsheet is “Protected” or being “Shared” with another file, the Data validation tool is turned off in Excel. Possible Reason 1: Protected or Shared – Fix Data Validation Greyed Out
0 kommentar(er)
