October 14th, 2011, 03:12 AM
-
Using OFFSET (to define dynamic range) in an INDIRECT function - Excel
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