Formulas in List of Data Validation

[shabangle]

Hi All,

I'm struggling to use a formula in the "List" area of Data Validation.

I will have sheets 1 to n, within my workbook, and each sheet has its sheet ID in cell A1.

I have a lookup sheet, with all the possible values I want to use in my data validation. Each sheet will require a different range of 7 values in the dropdown.

All the values are in column D of the lookup sheet, with my range of 7 possible values coming every 9 rows, e.g. for sheet 1 the range I want is D12:D18; sheet 2's range is D21:D27 etc. So the formula for this is "Lookup!D(9*A1 + 3):D(9*A1 + 9)".

How do I construct this range definition within the list area when defining the Data validation?

Many Thanks for any help
Andy

 

[Yongle]

You could use Named Ranges for your lookup ranges, and use those in DV formula
https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/

 

[FormR]

So the formula for this is "Lookup!D(9*A1 + 3):D(9*A1 + 9)".

How do I construct this range definition within the list area when defining the Data validation?

You could also try the INDIRECT() function - for example:

=INDIRECT("lookup!D"&9*A1+3&":D"&9*A1+9)

 

[shabangle]

Thanks for the quick replies on this, the INDIRECT is exactly what I'm after. Named ranges would be too complicated (I actually provided a much more simplified explanation) in this case. I also appreciate there is a VBA option, but in this case the indirect is exactly what I'm after.

Much Appreciated