Dynamically expanding the source range for a data validation list.

Hi all,

It's my first post here so please don't be too harsh if my post in miscategorized. I am working on a relatively simple nutrition tracking calculation worksheet.

I have two worksheets:

Worksheet A-contains a table with a list of foods and several columns that contain the nutrition information.

Worksheet B-contains food entry drop down lists and runs simple SUM() calculations on them and presents me with a chart of the data.

My question is the following; my food list drop down is populated from the food list table. How can I make it dynamically expand the selected range of cells whenever I add a new food (row) into my food table? I would like my source range selection to grow by the number of rows that I am adding to my table.

Any input is appreciated.

There's a couple answers to this that I'm aware of. I you (or the person editing the worksheet) is smart or will read instructions, you can always insert a cell in the middle of the list. The bottom cell will be moved down, but the list reference should simply expand to allow for new entry.

Alternatively, you could format the list as a table, and instructions on that vary by excel version. Which do you have?

create the table of dropdown list.then provide the range in the source of data validation.

When ever you want to add the new value just insert the new row in table and input tha value.The value will be present in the dropdown list

All,

Thanks very much for your input. I will try out your suggestions and let you know how it goes. I am building this application in Excel 2007.