Dilysu data dibynnol?

Dyfynnwyd o SituFangs ar ddydd Mawrth, 22 Mehefin 2021

Sut alla i greu dilysiad data mewn un gell sy'n dibynnu ar yr hyn sy'n cael ei nodi mewn galwad wahanol? Enghraifft: Yn A1 mae gen i luniad dilysu data o restr o bynciau ysgol. Gall y defnyddiwr ddewis un o'r pynciau o'r gwymplen. Bydd y gwymplen yn B1 yn amrywio yn dibynnu ar yr hyn a ddewiswyd yn A1. Os oes gan A1 "Mathemateg," bydd B1 yn darparu opsiynau o wahanol ddosbarthiadau mathemateg. Os oes gan A1 "Siarad Cyhoeddus", bydd B1 yn darparu opsiynau o wahanol ddosbarthiadau siarad cyhoeddus. 10.0.0.1

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

Dyma'r camau:

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.