Data validation with IF condition

Hello, I have created Yes/No drop down options in cell B2. If Yes is selected, Cell C2 should show up 3 options from drop down (data validation list). If No is seleced in cell B2, Cell C2 should show up as "Not applicable". How can I get this result? Please suggest.
My trial :

In Cell C2>data validation>list and in source, i typed the below formula :

=If(B2="No", "NA", Sheet2!Frequency) but it says "The source must be a delimited list or a reference to single row or column". I have already created a reference of 3 options for cell C2 by naming the range as "frequency".

Thats not the way DV works

With DV, you select the cell, and a list of values appears - select 1 of them

To show something else if another cell contains something (no), you would need a formula in C2. But when you select something from te DV, that formula would be over-written

The only way I can see to do this is with VBA. The code needs to check if 'Yes' or 'No' is in B2, then either apply DV or enter 'NA'.
I'm sure the following code could be compacted/tidied up a bit (I'm not a VBA expert) but it will work, with the list for your 'Frequency' DV in cells B1:B3 of a sheet called 'Drop-down entries'. It will only run when cell B2 is changed.

Please Login or Register  to view this content.

To use this code, click 'Alt-F11' to enter the VB-Editor. Right click on the name of the sheet you're working on (for example 'Sheet2 (Working Sheet)' ) and select 'View Code'. Copy the code above and paste it into the window on the right.

If you copy the code into your file, all you should need to do is change the range for the DV for C2 - in other words, change the bit in red:

Please Login or Register  to view this content.

The file will need to be saved as a macro-enabled workbook (.xlsm) for the code to run, and macros will have to be enabled.

Attached is a file with this code working. B2 and C2 are on the sheet called 'Working Sheet'. The lists for drop-down are on the sheet 'Drop-down entries' - there's 'Yes' and 'No' in A1:A2 (used for B2 on the Working Sheet) and 'Daily' 'Weekly' 'Monthly' in B1:B3 (used for the DV on C2 when 'Yes' is selected).

Hope that helps.

@llamafarmer
I considered that approach first, but went with VBA for two reasons:

1. the OP asked for 'NA' to appear in C2 if 'No' was selected - I assumed this was to be entered in the cell, not in a drop-down;
2. if 'Yes' is selected in B2, then 'Option1' in C2, then 'No' is selected in B2, the 'Option1' remains in C2 until it is actually changed - I assumed the OP would like the cell to be 'reset' when either Yes or No was selected.

I might be wrong, in which case your solution is good.