THis query is about using the INDIRET function (in the Data Validation section for generating a dropdown) along with a range which is created dynamically

Here is the scenario

I have data like this in a worksheet called 'Master'

Column A Column B
-----------------------------------------------------------
Requirement Review Incorrect requirement
Requirement Review Incomplete/ambiguous requirement
Requirement Review Irrelevant/extraneous requirement
HLD Review Incorrect design
HLD Review Incomplete/ambiguous design
HLD Review Redundant design
HLD Review Non-optimal design

I have created dynamic ranges to select the range from Column B corresponding to value in Column A

The 2 OFFSET function for dynamic range looks like this


RangeName: RequirementReview
=OFFSET($A$1,MATCH("Requirement Review",Master!$A:$A,0)-1,1,COUNTIF(Master!$A:$A,"Requirement Review"),1)

RangeName: HLDReview
=OFFSET($A$1,MATCH("HLD Review",Master!$A:$A,0)-1,1,COUNTIF(Master!$A:$A,"HLD Review"),1)

These two fuction will select the ranges "RequirementReview" = B1:B3 and "HLDReview" = B4:B7

Now I want to call this range in the INDIRECT Function depending upon a value in some cell (say A1)

But when I use the Indirect function as

=INDIRECT(A1) where A1 will contain either "Requirement Review" or "HLD Review"
But it does not generate the dropdown list

If I replace the Rangename Requirementreview as B1:B3 the indirect function works

Kindly suggest a way out as I don't want to use a macro