In the previous article, we recorded how to use the information list in different sheets as the drop-down option in Excel-key steps: 1. Define the name; 2. Set the data validity to "sequence", and the value range is equal to the name.
Today, another problem occurs. You need to define the range of options displayed in the last cell based on the options selected in the previous cell.
For example, in the attached Excel file, column H is "fixed expenditure", and column I is "expenditure category ".
There are two options for "fixed expenditure": fixed expenditure and floating expenditure. The setting method is described above. It can be seen that the list has been added to the "Basic settings" sheet as the option value. At the same time, there are two options: "floating expenditure" and "fixed expenditure". The problem is: when "fixed expenditure" is selected in column H, you need to display the fixed expenditure option in column I, when you select floating expenditure, the option of floating expenditure is displayed in column I.
The following methods are available:
1. Define three names respectively: expenditure category, floating expenditure, and fixed expenditure. The value range is the data in the three columns of "Basic settings.
2. Define the following data validity in column I: select the validity condition as "sequence" and enter the following formula in the source:
If ($ H2 = "fixed expenditure", fixed expenditure, floating expenditure)
Instance files
The current attempt is acceptable, but I don't know if there are any other good methods.