Reference 2 Dynamic Ranges for Data Validation

Giganews Newsgroups
Subject: Reference 2 Dynamic Ranges for Data Validation
Posted by:  retailmessiah (retailmessi…@gmail.com)
Date: Tue, 15 Jul 2008

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

Replies