List Validation Lookup

[marka87uk]

Hi,

I have a block of data in a sheet with two columns Group and Name. Each Group is a number in which a number of Names can fall into.

On a separate sheet I have a cell which will have a Group number in, and I wish for another range of cells to have a list validation but only of the Names in the one Group specified, i.e. the user should only be able to select names in the Group selected.

It would be too long of a process to define a name for each set of Names manually as the data could change.

How would you achieve this?

Many thanks in advance!

 

[T. Valko]

Hi,

I have a block of data in a sheet with two columns Group and Name. Each Group is a number in which a number of Names can fall into.

On a separate sheet I have a cell which will have a Group number in, and I wish for another range of cells to have a list validation but only of the Names in the one Group specified, i.e. the user should only be able to select names in the Group selected.

It would be too long of a process to define a name for each set of Names manually as the data could change.

How would you achieve this?

Many thanks in advance!

Is the source data sorted or grouped together like this:

Book1
	A	B
1	Group	Name
2	1	Biff
3	1	Sue
4	1	Lisa
5	2	Bob
6	2	Tom
7	2	Grace
8	3	Sam
9	3	Carol
10	3	Mike
Sheet1

 

[marka87uk]

Yes, that's exactly how it's grouped.

 

[T. Valko]

Yes, that's exactly how it's grouped.

Let's assume the source data is in the range Sheet1 A2:B10.

On some other sheet you enter the group number in cell A2 (or, this could be a drop down list of group numbers).

Create this named formula:

Name: List
Refers to:

=OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,Sheet1!$A$2:$A$10,0)-1,,COUNTIF(Sheet1!$A$2:$A$10,Sheet2!$A$2))

Then, as the source for the name drop down use: =List

 

[marka87uk]

Thanks very much for your help but I'm unable to get it working correctly (I assume I've done something wrong!).

The formula cell returns 0.

Any ideas what I'm doing wrong or would it be possible for you to post an example workbook?

Thanks!

 

[T. Valko]

Thanks very much for your help but I'm unable to get it working correctly (I assume I've done something wrong!).

The formula cell returns 0.

Any ideas what I'm doing wrong or would it be possible for you to post an example workbook?

Thanks!

OK, here's a small sample file that demonstrates this.

ZZZmarka87uk.xls 16kb

http://cjoint.com/?AGowsQ6RphZ

 

[marka87uk]

Thanks a lot!

I didn't understand what you meant at first with creating the named formula but I realise now what I needed to do.

 

[T. Valko]

Thanks a lot!

I didn't understand what you meant at first with creating the named formula but I realise now what I needed to do.

Good deal. Thanks for the feedback!