Odvisno preverjanje veljavnosti podatkov?

Citirano iz SituFangs v torek, 22. junija 2021

Kako lahko ustvarim preverjanje veljavnosti podatkov v eni celici, ki je odvisna od tega, kaj je vneseno v drugem klicu? Primer: V A1 imam risbo za preverjanje veljavnosti podatkov s seznama šolskih predmetov. Uporabnik lahko izbere enega od predmetov v spustnem meniju. Spustni meni v B1 se bo razlikoval glede na to, kaj je bilo izbrano v A1. Če ima A1 "Matematiko", bo B1 zagotovil možnosti različnih matematičnih razredov. Če ima A1 "Public Speaking", bo B1 zagotovil možnosti različnih razredov javnega nastopanja. 10.0.0.1

To achieve this in Excel, you can use named ranges and the INDIRECT function for data validation.

Spodaj so navedeni koraki:

1. Create named ranges for the different classes corresponding to each subject. For example, name the range for Mathematics classes as "Math_Classes", and name the range for Public Speaking classes as "PublicSpeaking_Classes".

2. Set up data validation for cell A1 to create a dropdown list of school subjects.

3. Set up data validation for cell B1, but instead of directly referring to a range, you will use the INDIRECT function to dynamically reference the range based on the selection in cell A1.

Here's how you can set up the data validation for cell B1:

1. Select cell B1.
2. Go to the "Data" tab in the Excel ribbon.
3. Click on "Data Validation" in the "Data Tools" group.
4. In the Data Validation dialog box, select "List" from the "Allow" dropdown menu.
5. In the "Source" field, enter the following formula:
`` `
=INDIRECT(A1 & "_Classes")
`` `
This formula dynamically references the named range based on the value in cell A1. For example, if "Mathematics" is selected in cell A1, the formula will refer to the named range "Math_Classes", and if "Public Speaking" is selected, it will refer to "PublicSpeaking_Classes".
6. Click "OK" to apply the data validation.

Now, when you select a subject in cell A1, the dropdown list in cell B1 will automatically update to show the corresponding classes for that subject.