MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with Data Validation

Posted by Eric Campion on August 26, 2001 3:27 PM

OK, I am trying to manipulate the data already created
for a cell that is a drop box. When I go to data
validation, it says it is to "allow" a "list" and
under "source" it says "=racelist". I found the data
in question on a another worksheet. Right now the
drop box will access the data in rows 3 through 22. I
need to change it so it will access the data down to
row 50, meaning the drop box will display more options.

AIM ID: erc1452
Thanks again for the help, I would be lost without ya!

Posted by Aladin Akyurek on August 26, 2001 4:46 PM


Activate Insert|Name|Define.
You'll see there racelist. Click on this name such that it is in the box for Names in Workbook.
Go the Refers To Box, change 22 to 50.

Click OK. You're done.


PS. No need to start another thread on the same thing.

Posted by Tom Urtis on August 26, 2001 4:49 PM


Your spreadsheet has a cell that is validated (will only accept) the contents of a named range of cells in rows 3 thru 22 on another worksheet. Here’s what you can do: Go to the worksheet containing that named “racelist” range, and highlight that entire “racelist” range, either by dragging your mouse over the range, or clicking the down arrow in the namebox (that white box near the upper left corner of your screen, to the left of the formula bar), and click on the word “racelist”. Now that the racelist range is highlighted, click on Insert > Name > Define, click on the word racelist in the large white box to highlight that word, then click the Delete button, then click the OK button. Next, highlight rows 3 thru 50 in whatever column that original racelist range was, and with all those cells highlighted, click on Insert > Name > Define, retype the word "racelist" in the “Names in workbook” box, click the Add button, and click the OK button. That will close the Insert Name dialogue box, and you will have redefined the range that is named “racelist”, by having it include up to row 50. Now you can enter data in rows 3 to 50 in the racelist range. Go back to the other worksheet and click on the still-validated cell, you will see all the new possible data options in that drop down box that you have entered in 3 to 50 of the racelist range.

Tom Urtis

Posted by Eric on August 26, 2001 4:49 PM


Posted by Tom Urtis on August 26, 2001 4:53 PM

Re: Thanks Aladin, good tip, I took the long route,