Data validation, drop-down box

ahanmagey · 10-06-2008, 03:26 AM

I want to do couple of things here.
1. put an empty field in a drop down box. If possible the empty field should be at the top from the drop down menu.
2. Update names in drop down box when I add names to the list

Thanks in advance

**royUK** · 10-06-2008, 03:53 AM

Please use the correct Forum, this is not a programming question

You need to convert the data list source to a Dynamic Named Range then the validation will update as you add to the list.

I can't see why you want an empty field in the list.

**royUK** · 10-06-2008, 03:55 AM

For help with Named Ranges see

http://www.cpearson.com/excel/named.htm

ahanmagey · 10-06-2008, 11:20 AM

Originally Posted by royUK

Please use the correct Forum, this is not a programming question

You need to convert the data list source to a Dynamic Named Range then the validation will update as you add to the list.

I can't see why you want an empty field in the list.

i am trying to do what you have said.
Why i want an empty cell??? lets say the sheet is a shoping list, one day the buyer did not want to buy any fruit so there should be no fruits selected. I know we can just press "delete" on fruits cell to empty it. This way i can manage BUT dear other guys who will use the sheet are ...... better not to say. They no nothing about excel or anything. So i have to make everything USERFRIENDLY and design in a way that NO MISTAKES can be done.

kind regards

**ChemistB** · 10-06-2008, 11:38 AM

To create the empty space at the top of your list, just use the spacebar in your first cell of your list. Unfortunately, if people are updating the data validation cell from previous use, the dropdown opens up to the last use (see attachment). I think I've seen some VBA solutions in this forum that fix that problem if it's an issue.

design in a way that NO MISTAKES can be done.

Now, that's the funniest thing I've heard all day. I have worked to minimize mistakes but as they say, "make something idiot proof and they'll just build a better idiot."

ahanmagey · 10-06-2008, 12:28 PM

Originally Posted by ChemistB

Now, that's the funniest thing I've heard all day. I have worked to minimize mistakes but as they say, "make something idiot proof and they'll just build a better idiot."

Hehehe i think u r right. i am realy ..**ed now, after thinking for such a long time about how to solve thses probs. But what to do my dear, the people working with me even deletes the simple SUM function by manually calculating the amount and writng it on the cell where the formula "SUM" is. Dats a big headach for me, correcting all those stuff again and again. Hahhhhh. anyway thanks for the help

**ChemistB** · 10-06-2008, 02:21 PM

You can protect your formulas by protecting your spreadsheet. First go through and "unlock" any cells where you want them to input data and then lock the spreadsheet. That will save a lot of headaches.

ahanmagey · 10-07-2008, 05:06 AM

Originally Posted by ChemistB

You can protect your formulas by protecting your spreadsheet. First go through and "unlock" any cells where you want them to input data and then lock the spreadsheet. That will save a lot of headaches.

What have you done to your list that makes it to update in drop down menu as you add names to the fruit list. Tell me in some details. ok . Thanks in advance.