Help and Guidance PLEASE. Cell Popultaes by formula BUT I also need a drop down???

[Mister H]

Hi All:

I am using Excel 2010 and I am looking for Guidance on the best way to accomplish a tasjk. I have a spreadsheet that based on the Office Location and type of credit card selected another cell will populate with a merchant number. All the info is pulled off a table. Here is a formula that I used that is working for me:

=IF(B60="CC",LOOKUP(LocationName,MinLocationName,CCmerchantNo),IF(B60="AMEX",LOOKUP(LocationName,MinLocationName,AmexMerchNo),""))

The problem I now have is that currently there are 20 LocationName on the table. Each location has a specific CCmerchantNo and a specific AmexMercNo

ONE LocationName now requires 2 CCmerchantNo and 2 AmexMercNo which means I can not use the formula for this area but instead need a drop down.

QUESTION: Is there a way of setting up the spreadsheet so that if one of the 19 locations that have only 1 number are selected then the MercNo is automatically filled in but if the otyher location is selected then the user MUST choose the MerchNo from a drop down list???

I have my doubts that i am being clear enough but HOPEFULLY someone understands my garbled request enough to tell me if it is doable and maybe point me in the right direction...?

THANKS,
Mark

 

[Mister H]

OK now I put the code in a module and it now Sort Of Works. All entries that I make in G2 Except for Big are bringing up the Input Box??? If I type in Large I am asked to:

Please enter number:
1. 4
2. 4

I just started playing around with the sheet so maybe I messed something up. Prior to your last post I had placed the code in the sheet1 instead of a Module.

Any idea why I the Inoput Box comes up for everything except Big?

THANKS,
Mark

 

[sijpie]

Don't understand. It should only throw up the messagebox if the entry appears twice in the list. It does that with me although what I sometimes have is that when entering something somewhere else on the sheet the box pops up as well, what i don't understand, and it is not consistent. But perhaps I can cure that.

But first we need to find out why it is coming up with the box when you enter a value which only appears once.

So we need to do some debugging: Open the macroeditor, double click on module1 to open the module (maybe it is already open). Now to the left of the function in the border click once so that a red dot appears and the line turns red. Yu have now set a breakpoint and the function will stop here when it runs.

Edit the cell F2 with a value that appears only once. When you press Enter the macroeditor pops up and the red line has a yellow arrow in front of it. Now by pressing the F-8 key you can step through the macro. So you can see what it is doing. At any point you can hold your mouse pointer over a variable to see what value it has.

Play with it to see if you can find what it is doing.