Hi
Although the title seems to hint towards creating dependent data lists (as in, if user selects state "CA" from a drop down in Column A, then the available choices in the drop down for Column B is retricted to cities in CA). My issue is a bit different.
Here's what I have, I have 3 columns (with following headers - FEATURE, MODEL C, MODEL S)
Column A - has a list of "ALL" possible features.
Column B - has "x" if the feature in A applies to the model C.
Column C - has "x" if the feature in A applies to the model S.
In a separate column F on this sheet (with header "FEATURES FOR C"), I am trying to have its values come from a drop down populated with just features applicable to Model C (those w/ "x" in it.) To achieve this I created a dynamic named range ("CFeatures") that is calculating the right "amount" of entries for this drop down, but it just does not honor the positioning. As in if there are 4 features that apply to Model C, the drop down is populating with the first 4 features in the list although the features are not the first 4 they are spread out.
Here are the formulas for the two dynamic ranges I have created are -
* AllFeatures
=OFFSET(features!$A$1, 1, 0, COUNTA(features!$A:$A)-1, 1)
* CFeatures
=OFFSET( INDIRECT( ADDRESS(MATCH("x",features!$B:$B,0), 1), TRUE ), 0, 0, COUNTIF(features!$B:$B,"x"), 1)
See the sample data below to get a better sense.
How can I modify the formula for the dynamic range to populate it with the right "ordered" set of features applicable to Model C - in this case it would be feature_x, feature_z, feature_c and feature_d. Currently it populates it with the first 4 (as in feature_x, feature_y, feature_z and feature_a) -OR- is there a better way to do it?
Thanks
SAMPLE DATA
--------------
COLUMN A: (Feature)
feature_x
feature_y
feature_z
feature_a
feature_b
feature_c
feature_d
COLUMN B: (Model C)
x
x
x
x
COLUMN C: (Model S)
x
x
--------------------------------------------------------------------
Although the title seems to hint towards creating dependent data lists (as in, if user selects state "CA" from a drop down in Column A, then the available choices in the drop down for Column B is retricted to cities in CA). My issue is a bit different.
Here's what I have, I have 3 columns (with following headers - FEATURE, MODEL C, MODEL S)
Column A - has a list of "ALL" possible features.
Column B - has "x" if the feature in A applies to the model C.
Column C - has "x" if the feature in A applies to the model S.
In a separate column F on this sheet (with header "FEATURES FOR C"), I am trying to have its values come from a drop down populated with just features applicable to Model C (those w/ "x" in it.) To achieve this I created a dynamic named range ("CFeatures") that is calculating the right "amount" of entries for this drop down, but it just does not honor the positioning. As in if there are 4 features that apply to Model C, the drop down is populating with the first 4 features in the list although the features are not the first 4 they are spread out.
Here are the formulas for the two dynamic ranges I have created are -
* AllFeatures
=OFFSET(features!$A$1, 1, 0, COUNTA(features!$A:$A)-1, 1)
* CFeatures
=OFFSET( INDIRECT( ADDRESS(MATCH("x",features!$B:$B,0), 1), TRUE ), 0, 0, COUNTIF(features!$B:$B,"x"), 1)
See the sample data below to get a better sense.
How can I modify the formula for the dynamic range to populate it with the right "ordered" set of features applicable to Model C - in this case it would be feature_x, feature_z, feature_c and feature_d. Currently it populates it with the first 4 (as in feature_x, feature_y, feature_z and feature_a) -OR- is there a better way to do it?
Thanks
SAMPLE DATA
--------------
COLUMN A: (Feature)
feature_x
feature_y
feature_z
feature_a
feature_b
feature_c
feature_d
COLUMN B: (Model C)
x
x
x
x
COLUMN C: (Model S)
x
x
--------------------------------------------------------------------
