How Can I Set Up Excel Data Validation for Different Customer Rates Without Macros?

Sheet 1 Sheet 2

A 11 A 101 B 22 B 202 C 33 C 303 D 44 D 404

In Sheet 3, cell A1 has a drop-down list of sheet 1 & sheet 2 names. When any sheet is selected, the table below should have the respective data validation. Try to create an invoice where each customer has different rates for the product.

Hi, I hope the attached Excel file is what you require. However, there is a minor issue with the data validation above. The problem lies in auto-refresh. When you make a selection, for example, Sheet1 and then you choose 11, the data validation cell will still display 11 even when you switch to Sheet2. Only upon selecting the dropdown will you see the necessary values for Sheet2. This setup could lead to errors if one forgets to click on the relevant data validation dropdown.

To automatically clear the dependent data validation, I believe macros need to be utilized. If there is any other solution available, I am unaware of it.

Thank you.