INDIRECT & XLOOKUP (or VLOOKUP if I have to for dependent dropdowns ...

[adambc]

I have followed Easy Steps Excel Dependent Drop Down List Data Validation &

to the letter ...

... but I still get this error How to avoid broken formulas - Microsoft Support if I enter =INDIRECT(XLOOKUP([@DeptShort],DeptShortName[DeptLongName],DeptShortName[DeptShortName]))

... and if I enter =INDIRECT("XLOOKUP([@DeptShort],DeptShortName[DeptLongName],DeptShortName[DeptShortName])") I get "The source currently evaluates to an error. Do you want to Continue?"

DeptShort is calculated using the XLOOKUP formula from another column (Dept) and if I add a column DeptShort and refer to cell in DeptShort direct ie =INDIRECT(columnrow) it works ...

Can anyone help me because i) I really don't want the DeptShort column and ii) I really do prefer names rather than cell references

Thanks ...

 

[adambc]

Anyone with any ideas why this isn’t working please?