Reference 2 Dynamic Ranges for Data Validation

  • Thread starter Thread starter retailmessiah
  • Start date Start date
R

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
 
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
 
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
 
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
 
Back
Top