Indirect not working for data validation in mac excel

[JSEAGES]

Hi All

I'm trying to link to a dynamic range based on the input in a different cell. I'm using indirects in data validation to call up the correct range. For some reason it isn't working even though I've done it hundreds of times before on a PC. I am on a Mac on Excel.

I have two 'Macro Groups' which are 'Income' and 'Expenditure'. These macro groups are input into Column D. In Column E, I want to select a 'Sub-Group' from my dynamic ranges for 'Income' and 'Expenditure'. My dynamic ranges are actually named 'Income_Groups' and 'Expenditure_Groups' so I am concatenating the indirect in the data validation box. Renaming the ranges to match exactly with the format in Column D is making no difference so I don't think the concatenation is causing the issue.

So in my data validation, assuming I am currently setting validation rules for cell E5 I've input List >> Source >> INDIRECT(D5&"_GROUPS") and it unfortunately isn't working.

Any help would be hugely appreciated.

Kind regards,
Joe.

 

[Aladin Akyurek]

Care to post the definition of Income_Groups?

 

[JSEAGES]

Care to post the definition of Income_Groups?

Hi

The 'Income_Groups' range is a dynamic range with the syntax =OFFSET(Lists!$F$3,0,0,COUNTA(Lists!$F$3:$F$100)). The dynamic ranges are working fine elsewhere when I directly reference the range (i.e. if I were to just write '=Income_Groups' in the 'Source' box within data validation, it works fine).

Hopefully that is clear?

Thanks,
Joe.

 

[Aladin Akyurek]

I thought that would be the case. INDIRECT does not work with references which are defined with OFFSET and kindred functions.

If you have just two options, try the following instead:

=CHOOSE(MATCH(D5,List,0), Income_Groups, Expenditure_Groups)

where List is a range consisting of subgroup names.

 

[JSEAGES]

I thought that would be the case. INDIRECT does not work with references which are defined with OFFSET and kindred functions.

If you have just two options, try the following instead:

=CHOOSE(MATCH(D5,List,0), Income_Groups, Expenditure_Groups)

where List is a range consisting of subgroup names.

This is working great - thanks so much for your help I really appreciate it.

 

[Aladin Akyurek]

This is working great - thanks so much for your help I really appreciate it.

You are welcome.