x
Solved

# Define Dynamic Excel Range Based on Column in Unsorted data set

Posted on 2014-02-04
Medium Priority
491 Views
Hello Experts,

I am having hard time trying to work this out. Maybe there is no simple solution for this. I have data in the following format.

Daily      1
Daily      2
Monthly      1
Daily      3
Monthly      2

I want to define 2 ranges which will select data based on first column. So a range named Daily will only select rows where the value in the first column is Daily. I can do it if the data is sorted but that is not going to be the case always.

Do you think this is doable?

0
Question by:spattewar

LVL 23

Assisted Solution

NBVC earned 1000 total points
ID: 39833866
You cannot do it unless it's sorted.  Perhaps you can extract the same info somewhere else sorted?  And use that reference instead...
0

LVL 81

Accepted Solution

byundt earned 1000 total points
ID: 39834433
If you don't have a gazillion rows of data, you can use an array formula to return "filtered" values. In the formula below, cell H1 contains "Daily" and the goal is to return a value from column B when column A also contains "Daily". When the list is exhausted, the formula returns an empty string (looks like a blank).
=IFERROR(INDEX(\$B:\$B,SMALL(IF(\$A\$1:\$A\$5=H\$1,ROW(\$B\$1:\$B\$5),""),ROWS(H\$2:H2))),"")

To array-enter a formula:
1.  Select the cell, then click in the formula bar
2.  Hold Control and Shift keys down
3.  Hit Enter, then release all three keys

If done correctly, Excel will respond by adding curly braces { } surrounding your formula. If you don't see them, repeat steps 1 to 3.

If you are using the "filtered" values in a data validation dropdown, you should do so with a named range using a Refers To formula like:
=\$H\$2:INDEX(\$H\$2:\$H\$100,MATCH("",\$H\$2:\$H\$100,0)-1)
DynamicFilteredListsQ28356442.xlsx
0

LVL 22

Author Comment

ID: 39851304

I ended up splitting the data on 2 different sheets.

Regards.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.