Dynamic Range problem 1

[Scott24x7]

All,
Ok, so I've been using dynamic named ranges for drop down data validation for a while now. BUT, I have a new problem. I have a list that I now need to "filter" for lack of a better term, based on a value in another field. The range is currently constructed with:

=OFFSET(INDIRECT("Ranges!$B$4"),0,0,COUNTA(Ranges!$B:$B)-1,1)

But, in Column H, I have now an "Active" or "Inactive" value (symbolized by and "X" if Active, and Blank if Inactive). In my drop down list on another worksheet, (Hours) I only want the values from Column B to appear in my list, where Column H has an "X" in it. It this possible?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Please post a sample of your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

A   Time    Code     Active
1  08:00    Morning    X
2  12:00    Lunch
3  14:00    Afternoon  X
4  18:00    Evening    X
5  22:00    Night      
6  24:00    Late Night X

Where column C is what I want to display, and column D is what I want to decide to use or not use in the drop down.

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Sort by Active then A.

In your OFFSET function, your row offset value in arg 2 is the count of Active equal to X, and your row count value in arg 4 is count of Active not equal to X.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

I can't sort the sheet before the sort, as it will impact other items on the sheet.

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Then use MS Query to return the subset of data you need.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Ok but how do I get that into the dynamic named range or do I use something else in the data validation clause?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Either put your query on the same sheet, without returning column headings, using the range name for the query, or put the query on another sheet and use a dynamic named range to define the resultset.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Is that the same as making a pivot table? I didn't know I could query within the sheet, only from external source?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

The sheet is treated as an external source.

Yes, you can query OTHER workbooks AND the workbook you are in.

You could use a PT, I suppose. I almost never do under these circumstances.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
Thanks, I didn't know that but will give it a try. I did try the pivot table, and it worked really well, just had to adjust my range formula to subtract 2 instead of 1 from the COUNTA to drop the "Grand Total" value. But I'm looking forward to try a query against it as well.


=OFFSET(INDIRECT("Ranges!$B$4"),0,0,COUNTA(Ranges!$B:$B)-2,1)


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

That will work. Thanks!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}