Solved - Problem with dynamic submenus - cant achieve a static reference from one sheet to another

📌 The Problem Explained

In many productivity workflows, users want to create “Dependent Dropdowns” (Submenus). For example, if you select Fruit in Column A, you want Column B to only show Apple or Banana.

The issue arises when your data is split across different sheets. Google Sheets data validation usually requires a specific cell range. If you try to point a dropdown to a formula that calculates a range dynamically on another sheet, the reference often breaks or becomes “non-static,” meaning it doesn’t stay tied to the correct category as you drag the formula down. Users frequently see the “Invalid Range” error because the validation field does not natively support row-relative formulas like INDIRECT across sheets without a specific setup.

💡 The Quick Solution

To fix this, you must use Named Ranges combined with the INDIRECT function. This creates a “static” global bridge that Google Sheets can follow regardless of which sheet the data is on.

In the Data Validation “Dropdown from a range” criteria, use this logic:

=INDIRECT(SUBSTITUTE(A2, " ", "_"))

(Note: Use A2 or whichever cell contains your primary category. The SUBSTITUTE part ensures that category names with spaces, like “Fruit Juice,” don’t break the reference.)

🛠️ Step-by-Step Breakdown

Step	Action	Description
1	Prepare Reference Sheet	Create a sheet named DataStore. In Column A, list Categories. In Columns B, C, etc., list the sub-items under headers that match the Category names.
2	Define Named Ranges	Highlight the sub-items for a category. Go to Data > Named ranges. Name the range exactly what the category is called (e.g., “Fruit”).
3	Create Primary Dropdown	In your main sheet, select the cell. Go to Insert > Dropdown. Set the criteria to Dropdown (from a range) and select your Category list from DataStore.
4	Apply Indirect Submenu	Select the Submenu cell. Go to Data > Data validation > Add rule. Choose Dropdown (from a range). In the range box, enter the formula =INDIRECT(A2).
5	Lock Sheet References	If referencing a specific cell on another sheet for the logic, use the $ symbol (e.g., Sheet2!$A$2) to keep the reference static where necessary.

⚠️ Common Mistakes to Avoid

1. Spaces in Names: Named Ranges cannot contain spaces. If your Category is Office Supplies, your Named Range must be Office_Supplies. If you don’t use the SUBSTITUTE function in your formula to replace spaces with underscores, the link will break.
2. Case Sensitivity: Google Sheets Named Ranges are not case-sensitive, but the text in your primary dropdown must match the name of the range exactly to trigger the INDIRECT function correctly.
3. One-by-One Validation: Unlike standard formulas, you cannot always “drag down” dynamic data validation for submenus in the same way. You may need to use a helper column or a Google Apps Script if you have thousands of rows requiring different dynamic logic.