Excel Nightmare (Easy Question)

[Barks]

I'm using Office for Mac OSX 2008... Excel.

I have a spreadsheet and I want drop-down menus on some of my cells with a choice of options.

In a separate spreadsheet I create a list of options, select them all and then simply type something, e.g. DRINKS into the Formula bar. So I'm selecting a group of cells and calling them DRINKS.

In my main spreadsheet I click on a cell and click Data Validation. In Criteria I select 'List' and then simply type =DRINKS in source.

Voilla, it works! However, how do I actually change what is in the DRINKS group? Say I want to add more, I can't make any changes and if I type DRINKS again it just reverts to selecting the list without the additions. What's the quickest and easiest way of either modifying or deleting my DRINKS cell group?

Many thanks, would be really grateful

 

[Participant]

In Office 2007 on the PC, you go to name manager and extend the cells covered by the named range:

I think you can do dynamic named ranges too, so that anything you add to the list gets automatically added to the range. I think if you define the range as a table then this works.

 

[Barks]

In Office 2007 on the PC, you go to name manager and extend the cells covered by the named range:

I think you can do dynamic named ranges too, so that anything you add to the list gets automatically added to the range. I think if you define the range as a table then this works.

Thanks mate you pointed me in the right direction.

You click Insert > Name > Define on the Mac version.

Cheers

 

[Jed Shields]

To define a dynamic range you need to add an indirect formula. I've got something somewhere, I'll dig it up tomorrow.

EDIT: or OFFSET ... one of the two

 

[Jed Shields]

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

http://www.contextures.com/xlNames01.html#Dynamic