Reference 2 Dynamic Ranges for Data Validation

[retailmessiah]

Hi Excel group,
You all are wonderful. I have one here that I feel like should be
easy, and I just know I'm overlooking something simple. I have two
lists as follows:

A1:A5:
John
Jim
Mark
<Blank>
<Blank>

B1:B5:
Charles
Steve
Mike
Kirk
<Blank>

I have named ranges setup like:
ChicagoOffice:
=OFFSET(A1:A5,0,0,COUNTA(A1:A5),1)

SeattleOffice:
=OFFSET(B1:B5,0,0,COUNTA(B1:B5),1)

I need to then create a named range for use in data validation that
pulls from both of the other dynamic ranges. Also, the names (in each
office range) change frequently. So I need a dropdown list to have all
the names excluding the blanks. If I specify either of the dynamic
ranges ChicagoOffice, or SeattleOffice I get the applicable names in
that range, less the blanks. I assume creating a third all inclusive
named range that references the other two dynamic ranges would be
best. I just can't figure out how to produce a combined list. Can
someone enlighten me on how to do this?

Thanks so much,
John

 

[Billy Liddel]

Hi

Debra Dalgliesh shows you how to do this, using lists and the INDIRECT
function.

http://www.contextures.com/xlDataVal02.html

incidently the range names could be written:

Chicago =OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet3!$A:$A)-1)
Seattle: =OFFSET(Chicago,0,1,COUNTA(Sheet3!$B:$B)-1)

There is also a sample file to download

Peter

Regards
Peter

 

[retailmessiah]

So, I see how you updated the ranges so that Seattle pulls from the
position of Chicago. That's a good use of the OFFSET function, but
doesn't seem to accomplish what I'm trying to do. The link that you
provided is one that I looked at already, and I found it to not be
applicable. I'm looking to have a list with everyone from Chicago AND
Seattle in one list. I'm not looking to first select the office, then
select the employee's name. I want one single drop down that will pull
in all of the data from the Chicago name range, and one from the
Seattle name range. I was also going to research how to sort the list,
but I bet there is an excel function that will do this. Basically I
need to be able to select from all employees nationwide in 1 list. So,
I'd be looking to create a new named range called BothOffices, and
somehow have the Named Range definition be like
=ChicagoOffice&SeattleOffice, although that doesn't work. Does that
make sense?

Thanks for the help,
John

 

[Billy Liddel]

I'm afraid you will have to start off with a complete list - perhaps copied
from your other two lists.

Chicago, Fred
Chicago, Sally
Seattle, John
Seattle, Mal

Lists sorted by city and Employee. Download DataValMgrProd.xls from the
contextures site, I'm afraid that I can't remember the link but formulas are
used in the data Validation to give you the options based on your first
choice (City) the example copied from Debra's workbook is:

=IF(D5="",OFFSET(MgrStart,MATCH(B5,MgrColumn,0)-1,1,COUNTIF(MgrColumn,B5),1),C5)

Hope this helps

Peter