Dynamic Named Ranges with dynamic starting points?

[kjlorfeld]

I have a table with two columns - departments and employees. There are multiple departments, and multiple employees within each department. For example:

Dept. Employee
1 Bob
1 Mike
1 Sally
2 Tom
2 Karen
3 Joe
4 Katie

I'd like to create a named range (to use in data validation) that is driven off of whatever department a user selects. If they select department 1, the range would be B2:B4, if they select department 3, the range would be B7:B7. I've seen dynamic named ranges using offset (and maybe index?) but it seems they all rely on a static starting point. Any thoughts on how I might be able to do this?

Thanks!

 

[anand_babu123]

Hi

Would you be able to afford a helper column ? If so i've got a solution..

1. Assume your data is from A1:B8 and the criteria cell is C1 (where you need to enter your dept id)

2. paste the below formula in cell D1.

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$C$1,ROW($A$1:$A$8),""),ROW(A1)),0),"")

and drag it till D8.

3. Now enter any dept id in cell C1 and you can see the column D will show the data only corresponding to the dept id given.

4. Now select this range (D1:D8) in data validation.

Thanks,
Anand

 

[ChrisR]

Hi,

Hopefully this will do what you need...............

Say this is your data

Excel 2010
	A	B
3	1	Bob
4	1	Mike
5	1	Sally
6	2	Paul
7	2	Peter
8	3	Fred
9
Sheet1

Use this formula in another cell (For my purpose it's in G7)
=ADDRESS(MATCH($E$4,$A$1:$A$8,0),1)

E4 will contain the dept. number

Then..........In Name manager add this formula to a name

=OFFSET(INDIRECT(Sheet1!G7),0,1,COUNTIF(Sheet1!$A$3:$A$8,Sheet1!$E$4))

The G7 relates to the first instruction........

Then just do a Data validation --> List and use the name range that you used for the offset.............

I've got this working and it works fine...........

Regards,
Chris

 

[Jonmo1]

try

=OFFSET($B$1,MATCH($D$1,$A$2:$A$8,0),0,COUNTIF($A$2:$A$8,$D$1),1)

D1 is the cell to enter the Department #.
B1 is the HEADER for column B (employee)
A2:A8 are all the departments.

 

[aaalviny]

I have a table with two columns - departments and employees. There are multiple departments, and multiple employees within each department. For example:

Dept. Employee
1 Bob
1 Mike
1 Sally
2 Tom
2 Karen
3 Joe
4 Katie

I'd like to create a named range (to use in data validation) that is driven off of whatever department a user selects. If they select department 1, the range would be B2:B4, if they select department 3, the range would be B7:B7. I've seen dynamic named ranges using offset (and maybe index?) but it seems they all rely on a static starting point. Any thoughts on how I might be able to do this?

Thanks!

what's your ultimate use of that range?

I think there should be some better formulas to achieve that aim, which you do not need to refer that specific range