Dynamic named range

**meddyliol** · 02-19-2010, 09:53 AM

Hi, I am new to this forum and have not used Excel for many years so have forgotten most of it. My problem is this, I am trying to create a drop down list box with expandable names. I want to put the list of names on a separate sheet and then 'point' to this from sheet one.

I have looked all over the place for help but to be honest nothing has worked so far, this is more than likely down to me. The list must be expandable so that I can add more names to it. The Dynamic Named Range seems to be what I require but I do not really understand it and cannot get it to work. I can get a drop down list to work by putting the list of names on the same sheet and using the Data, Validation and sheet facility. The trouble with this is, apart from having the list of names on the main spreadsheet which I think is messy, if I want to expand the list I will have to re-validate it again. At least I think I will.

I hope this isn't too garbled

Thanks in anticipation

Brian

**EllBol** · 02-19-2010, 12:28 PM

Welcome to the board.

Try this idea adjusting locations to suit.

On Sheet1 column A type a list of names.

Cell D1 - Select Data Validation
.
Allow list.

Source Select whole of column A or 'many' cells.

Check that this works OK.

Copy D1 and paste to position on required page. (Say A1)

Select A1 open Data Validation and change source from =$A$1:$A$125 to=Sheet1!$A$1:$A$125

or =$A:$A to =Sheet1!$A:$A

This should now work.

D1 on sheet1 can be left or removed to suit.

Hope this helps

EllBol

**meddyliol** · 02-19-2010, 01:56 PM

OK, thanks for your answer. I will try that and see. Will let you know the outcome.

Thanks again

Brian

**meddyliol** · 02-19-2010, 02:35 PM

Well I tried it. Sheet 1 works fine but after copying to sheet 2 and changing source to
Sheet1!$A$1:$A$30. When I tried the drop down list all it comes up with Sheet1!$A$1:$A$30 and not the list of names on sheet 1.

Any ideas?

Cheers

Brian

**DonkeyOte** · 02-19-2010, 02:47 PM

For general info. on creating Dynamic Named Ranges see the link in my sig. to Debra Dalgleish's tutorial (@ contextures).

**EllBol** · 02-19-2010, 06:56 PM

Just retried my suggestion and it works perfectly.

I am using Excel 2007. Could it be the Mac version is slightly different?

EllBol

**meddyliol** · 02-20-2010, 04:20 AM

I am using Excel 2008 for Mac so it could be slightly different. I have a PC laptop as well with Windows XP on, will try it on that later on.

Thanks again for everybody's help.

Brian

**DonkeyOte** · 02-20-2010, 04:24 AM

Brian, as mentioned previously if you want a dynamic range that resizes the list to reflect the count of items see the prior link - it's the best tutorial around on the subject.

In most basic terms - assume I add values to A1 onwards on Sheet2

Code:

Name: _Names
RefersTo: =OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A),1)

or if you prefer non-volatile approach and assume entries in A are always text

Code:

RefersTo: =Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH(REPT("Z",255),Sheet2!$A:$A))

Now on Sheet1 in cell B1 I want to use the above so I create a DV List with source set as: =_Names

EDIT:

FWIW, EllBol's earlier suggestion will not work if the DV cell is not on the same sheet as the list source - create a Name that reflects the range and use that.