Reference 2 Dynamic Ranges for Data Validation

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
0
7/15/2008 2:41:34 PM
excel 39879 articles. 2 followers. Follow

3 Replies
632 Views

Similar Articles

[PageSpeed] 57

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" wrote:

> 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
> 
0
BillyLiddel (193)
7/16/2008 11:28:59 AM
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
=3DChicagoOffice&SeattleOffice, although that doesn't work. Does that
make sense?

Thanks for the help,
John

On Jul 16, 6:28=A0am, Billy Liddel
<BillyLid...@discussions.microsoft.com> wrote:
> 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 =3DOFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet3!$A:$A)-1)
> Seattle: =3DOFFSET(Chicago,0,1,COUNTA(Sheet3!$B:$B)-1)
>
> There is also a sample file to download
>
> Peter
>
> Regards
> Peter
>
>
>
> "retailmessiah" wrote:
> > 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:
> > =3DOFFSET(A1:A5,0,0,COUNTA(A1:A5),1)
>
> > SeattleOffice:
> > =3DOFFSET(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- Hide quoted text -
>
> - Show quoted text -

0
7/16/2008 12:16:37 PM
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



"retailmessiah" wrote:

> 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
> 
> On Jul 16, 6:28 am, Billy Liddel
> <BillyLid...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
0
BillyLiddel (193)
7/16/2008 9:11:01 PM
Reply:

Similar Artilces:

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Reference Book
I'm a Win32 programmer, and I'm trying to get into some MFC. I've read a little about MFC, but the books lack a lot of quality and descriptive text about the structure of MFC. Can I ask whose book is the Charles Petzold of MFC? Thank you I haven't seen a bad MFC book in a long time, but then again, I haven't bought a new MFC book in many years. My presonal perference are the MFC books published by Wrox Press http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=1861000855&itm=31 You can't go wrong with the Microsoft Press ones either. Al...

VCR to DVD Part 2
Many thanks for previous answers. Yes, finalizing the DVD worked I found that DVD-R need finalizing but DVD+RW disks do not But why was I able to play the unfinalized DVD-R on one machine and not on two others. What was special about the first PC? -- www.stfx.ca/people/bliengme Bernard Liengme wrote: > Yes, finalizing the DVD worked > I found that DVD-R need finalizing but DVD+RW disks do not > But why was I able to play the unfinalized DVD-R on one machine and not on > two others. > What was special about the first PC? Almost anything will play a finalised D...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

CSliderCtrl Dynamic Orientation
Is it possible to change the orientation of a slider after creation? I cannot figure anything out besides making a new window with a new slider. Any help would be greatly appreciated. Thanks, Greg I can change the slider control orientation using CWnd:ModifyStyle() but when I proceed to adjust the dimensions of the CSliderCtrl using SetWindowPos the slider vanishes even with a repaint. Any ideas? "GBayard" wrote: > Is it possible to change the orientation of a slider after creation? I > cannot figure anything out besides making a new window with a new slider. >...

set print area #2
Where is the "set print area" button in Excel 2002 that was in Excel '97? =?Utf-8?B?Q2Fyb2wgTWM=?= wrote > Where is the "set print area" button in Excel 2002 that was in Excel '97? > Are you sure it was "standard"? Only way I could get in was to right-click the toolbar|Customise and drag it to the toolbar from Commands|File -- David ...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

OEM publisher 2002 Sp1-2
I have the OEM version of Publisher 2002 and cannot find the Export as web page under File. Is there a way I can get that feature with a standalone disk? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Chicagogal <anonymous@discussions.microsoft.com>... > I have the OEM version of Publisher 2002 and cannot find the Export > as web page under File. Is there a way I can get that feature with a > standalone disk? The OEM version of Publisher is exactly the same as the release in every way but the license, so the standalone version will be no differen...

GP SP#2 hangs with sa and Collections Issue
I just installed 8.0 SP#2 (ran the script that it says to run cuz we had upgraded from previous version to remove dups in smartlist tables) and when I log into GP as sa, it hangs and CPU sits at 100%. When I was able to log in previously to SP#2, I constantly received errors about the CN_Collector_MAIN table with invalid column names etc. I've deleted the table, then re-ran the create tables, but it keeps coming back with this error. I've even gone into file > maintenance > sql and deleted and recreated the table, but then get "number of results doesn't match ...

Configure Microsoft Dynamics CRM for Outlook
While trying to configure Ms CRM for outlook throws an error message HTTP Status 401: Unauthorized.Can anyone put me out of this issue. ...

Missing Reference
Argh! There was a missing reference, " Microsft DAO2.5/3.5 Compact ..." I unselected it and backed out - now, every date field is filled with today's date and several other fields have gone to the 'dark side'. The $ field shows $0.00 for everyone and another field is blank. I went back into the dialog box and I can't find that reference in the list to reselect it. Thank goodness I experimented using a copy, eh? >-----Original Message----- >Your references are probably messed up. >If any of the selected references have "MISSING:" in front ...

Changing ip address of exchange server #2
Hi, I have a back-end server and a smtp server in DMZ. I want to change ip address of back-end server. are there any issues? all incoming and outgoing emails are going via smart host. Hi, No issues at all as long as you remember to change all the references to this server in your firewall, SMTP scanner etc. Leif "Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message news:FE5927A1-D20D-4C6B-991F-2E1EFD19434D@microsoft.com... > Hi, > > I have a back-end server and a smtp server in DMZ. > > I want to change ip address of back-end server. are ...

How can I print on 8 1/2"x17" paper?
I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to change the paper size to accept and print anything bigger than 14." any suggestions would be greatly appreciated First make the size settings in the Printer Setup. You may need to use the Custom selection. -- Don Vancouver, USA "mickilena" <mickilena@discussions.microsoft.com> wrote in message news:1AD99AFE-7FE7-4A07-9D20-1B1AFF9856BD@microsoft.com... >I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to > change the paper size to accept a...

China + Dynamics GP
We are opening a distribution center in China and was wondering if anyone else is currently using Dynamics GP and any form of EDI process with China. We are considering what would be the best way of handling. Due to security risks, we are wary of allowing RDP/term serv access. So one idea was to give them a laptop with GP/Sql Server/Integration Manager. Since they couldn't connect to us they would have a separate database. This would then involve exports and imports between the 2 company locations to a neutral site (such as FTP). Not sure yet if we will be mirroring the datab...

when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is called Book1 and the other document is the actual document that I want to open. How do I stop this from happening? Brian To prevent Book1 from opening you can append /e to your shortcut for opening Excel. "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e Alternative to the above............ Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel Worksheet. Then if running Win98 OS Edit> select "Open" and Edit. If using WinXP OS you would scroll down to...

Money Updates #2
Was wondering about money updates. What exactly is the program downloading during the update process? And if the program is updated on a daily basis, why would I ever need to upgrade to a newer version? In microsoft.public.money, invalid wrote: >Was wondering about money updates. What exactly is the program downloading >during the update process? And if the program is updated on a daily basis, >why would I ever need to upgrade to a newer version? It is mainly changes in online bank setup. Despite appearances is typically does not change anywhere daily. "invalid" <...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

Does POS 2.0 support price-embedded barcodes?
Does anyone know if Microsoft Point of Sale 2.0 supports price-embedded barcodes (also known as random weight barcodes)? I have Digi scales that print out 12-digit barcodes with UPC and price info. I would like to implement POS and not RMS because it is much less complex. Thanks for any info. POS 2.0 does not support price-embedded barcodes. "Martin" wrote: > Does anyone know if Microsoft Point of Sale 2.0 supports price-embedded > barcodes (also known as random weight barcodes)? > > I have Digi scales that print out 12-digit barcodes with UPC and price info. &...

ANN: Optimizing the Performance of Microsoft Dynamics CRM 3.0 white paper
Hi, the White Paper "Optimizing the Performance of Microsoft Dynamics CRM 3=2E0" is now available: http://go.microsoft.com/fwlink/?LinkID=3D80916=20 Regards Nicolas F=FChrs ...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...