I am creating a dependent drop down where the drop down itself is located on the first tab of the sheet and the data that includes the dropdown options is on another tab. When the drop down and the data are on different tabs, I get the following error when entering the index match function into the data validation list formula:
"You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria."
The formula I am using is the following (which works when it is not in the data validation list formula section):
=INDEX(sheet4!$A$2:sheet4!$BG$15,,MATCH($A8,sheet4!$A$1:sheet4!$BG$1,0))
When I move the data I am referencing to the same tab and enter an updated formula to the data validation list section, then I don't get an error.
This makes me think that the error is occurring because of my "Sheet4!" references in the original formula. Is there a workaround to have a dependent drop down reference data on a separate excel tab?
"You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria."
The formula I am using is the following (which works when it is not in the data validation list formula section):
=INDEX(sheet4!$A$2:sheet4!$BG$15,,MATCH($A8,sheet4!$A$1:sheet4!$BG$1,0))
When I move the data I am referencing to the same tab and enter an updated formula to the data validation list section, then I don't get an error.
This makes me think that the error is occurring because of my "Sheet4!" references in the original formula. Is there a workaround to have a dependent drop down reference data on a separate excel tab?
