Prev: Using validation to make data appear or not
Next: protect marker
From: abc on 28 Feb 2010 00:43
I have Excel 2007 and am attempting to use the data validation tool. I have
created several lists in a separate worksheet and named them (In the name
box). My drop down and options have shown correctly. However, I now need to
make a few changes to the lists. I added a few extra cells with data to one
of the lists and would like these options included in the drop down field on
the main spreadsheet. Can someone tell me how to re-name my 'source' (list
or range of cells) so that the added cells are now being referenced as well
and all options show in the drop down?
From: OssieMac on 28 Feb 2010 02:42
Select the worksheet with the defined names.
Select Formulas ribbon.
In the Defined names block, Click Name manager.
Select the name to be re-defined.
Click Edit
In the Refers to field, click the icon at the right with the red arrow
pointing towards top left.
Select the entire new range on the worksheet.
Click the icon at the right of the field with the down pointing red arrow.
Click OK then Close.



Alternatively to selecting the range for the Refers to field, you can just
edit the range in the Refers to field. Click in the field and then press F2
and edit the range as required. (If you don't press F2 to edit then your
arrow keys will not work properly if you want to move the cursor within the
range.)


--
Regards,

OssieMac


"abc" wrote:

> I have Excel 2007 and am attempting to use the data validation tool. I have
> created several lists in a separate worksheet and named them (In the name
> box). My drop down and options have shown correctly. However, I now need to
> make a few changes to the lists. I added a few extra cells with data to one
> of the lists and would like these options included in the drop down field on
> the main spreadsheet. Can someone tell me how to re-name my 'source' (list
> or range of cells) so that the added cells are now being referenced as well
> and all options show in the drop down?
From: WallyWallWhackr on 28 Feb 2010 10:57
On Sat, 27 Feb 2010 21:43:01 -0800, abc <abc(a)discussions.microsoft.com>
wrote:

>I have Excel 2007 and am attempting to use the data validation tool. I have
>created several lists in a separate worksheet and named them (In the name
>box). My drop down and options have shown correctly. However, I now need to
>make a few changes to the lists. I added a few extra cells with data to one
>of the lists and would like these options included in the drop down field on
>the main spreadsheet. Can someone tell me how to re-name my 'source' (list
>or range of cells) so that the added cells are now being referenced as well
>and all options show in the drop down?


Select the Formula ribbon, and then the "name manager", edit as desired
those names and ranges you need to change.

Just so you know, if you INSERT a row into a named range, it expands it
automatically. Rows added AFTER the range do not get caught, and require
manual editing.

I have a time increment elector that allows one to switch a dropdown
list by the selection made in another cell.

It is on the MS user submitted templates area at:

http://office.microsoft.com/en-us/templates/TC300083081033.aspx?CategoryID=CT101172771033
 | 
Pages: 1
Prev: Using validation to make data appear or not
Next: protect marker