first option in a drop down menu

Where's the blank space coming from?

If there is an entry in cell A1 when you click the drop arrow the list will automatically go to the entry that's in cell A1.

If cell A1 is empty when you click the drop arrow the list will be displayed starting from the first item on the list.

If you're getting a blank space then there must be an empty cell in the source for the drop down list.

Where is it? Is the source a dynamic range?

You could use a lookup function for the "alien value". However, a cell can not contain both a formula and a drop down list (for the "non-alien" values) at the same time.

You could use an event macro to do it. However, I'm not much of a programmer so if you wanted to go that route then someone else will need to help you.

Maybe use one set of cells for the lookup of "alien values" and use another set of cells for the "non-alien" drop down list.

Originally Posted by dawidski

Is there any way to maybe use a check box. If my box is checked(true) then it would display the lookup function. It it is unchecked(false) it would display a drop down menu?

That would still require programming.

If your intention is to change which formula is represented with programing through a vba change event, it's pretty straight forward. It can be done 1 of 2 ways. 1st, you can have the vba code actually write the formula as you intend it to be written, or 2nd, you can combine both of the formulas into an if statement that looks at a cell value and displays formula 1 vs formula 2 based on that cell value, which can be updated via the change event. The 2nd will be easier to code, the first will be less intrusive in your workbook and will make the accidental deletion or change of said formula have no affect on the result because it will just be re-written every time the code runs. I would recommend the first option, especially if the formula is relatively simple.

Now that I actually looked at your workbook the scenario is a little different than I first thought. How are the sources for the drop down boxes defined? (I'm very unfamiliar with dropdown boxes...) Instead of having a static source, if it is possible for the source of the dorpdown box to reference a range of formulas, then I would use a range of formulas that changes the information available to the source based on the first drop down box value.

Try adding this to the worksheet code:

Please Login or Register  to view this content.

Right click on the sheet, choose "view code", then paste this code.

This changes the options in the 2nd dropdown box, but for some reason not the others. Like I said, I don't know much about drop down boxes, so there is probably something about their current design/layout that I'm missing here that needs to be further addressed.

Sorry, I just realized that I pasted the formula, not the code in that last post. If you're having problems, that's why...

Learning as I go here, but I just figured out why the 3rd and 4th dropdown boxes weren't responding to changes in cell B3. It's because their sources were =INDIRECT(C3) and =INDIRECT(D3) respectively (references to blank cells) instead of =INDIRECT($B$3).

I've almost exclusively learned what I need by searching this forum for my questions. It's been very helpful. http://www.ozgrid.com/ is another good one.

If you need to know how to change those sources, it's done by selecting the cell with the dropdown box, then through the Data menu, choose Data Validation, then Data Validation again, then the source is very obvious.

Also, if you want 10% to show up 1st in the lists instead of 40 when you choose "not an alien", then cut and paste the 10%-200% values from cells Q1:Q20 into cells Q4:Q23 so that the first cell lines up with the dropdown box row.

I can certainly try, though it probably won't happen tonight. (I'm about to get off the computer for the evening.) But go ahead and post your question. If it is vba related though, I would put it in the programming forum here: http://www.excelforum.com/excel-programming-vba-macros/, and paste a link to this thread.