Dependent drop downs on Sheet1.
First drop down in B2 & source is =Division_List
Second drop down in C2 & source is INDIRECT(B2)

Division_List is a list of 16 named ranges DIVISION_1 , _2 , _3 ... _16. And these are on another sheet.

Those names are the Headers of 16 columns on sheet CSI_DETAILED with each list row 2 to a variable row.
All work fine with non dynamic formulas for the ranges.

Want to swat to dynamic ranges for each DIVISION_n

This refers to named range DIVISION_1 on the sheet CSI_DETAILED.

=OFFSET(CSI_DETAILED!$D$2,0,0,COUNTA(CSI_DETAILED! $D:$D),1)

Does not evaluate to an error but the value in the name manager = (...).

Works in a sample drop down on the CSI_DETAILED sheet, shorter or longer list is picked up in the test drop down.

Should I be remembering that you cannot use the offset formula from another sheet for this?

I googled plenty of samples and the formulas are the same, but no mention about other sheets/off sheet.

Thanks,
Howard