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

#### Mister H

##### Well-known Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### sijpie

##### Well-known Member
If you can and want to use macros then probably the best way is to set up a UDF (User defined function) that will select the correct value if there is only one entry in the table or request userinput if there are two (or more)

But you should also consider if you can you can split up the one location in an A or B (the user needs to know anyway, since he is required to select form the dropdown box) - This would be the easiest solution

#### sijpie

##### Well-known Member
I have been playing about with a UDF, and here is a function that will do somethng like what youwant. It may need a little tinkering.
Code:
``````Function VGetOutp(rIn As Range, rLookup As Range, iOffset As Integer) As Variant
Dim rStrt As Range, rFnd As Range, rNxt As Range
Dim Outcome As Variant

Set rStrt = rLookup.Cells(1, 1)
Set rFnd = rLookup.Offset(-1, 0).Find(rIn.Value, after:=rStrt)
If Not rFnd Is Nothing Then
Set rNxt = rLookup.Find(rIn.Value, after:=rFnd) 'FindNext(after:=rFnd)
If Not rNxt Is Nothing Then
If rNxt.Row > rStrt.Row Then
' two values found
Dim sPrompt As String
sPrompt = "Please enter number:" & vbCrLf & _
"1. " & rFnd.Offset(0, iOffset).Value & vbCrLf & _
"2. " & rNxt.Offset(0, iOffset).Value
Outcome = Application.InputBox(sPrompt, "Please choose correct item", Default:=1, Type:=1)
Select Case Outcome
Case 1
Outcome = rFnd.Offset(0, iOffset)
Case 2
Outcome = rNxt.Offset(0, iOffset)
Case Else
Outcome = False

End Select
Else
Outcome = rFnd.Offset(0, iOffset)
End If
Else
Outcome = rFnd.Offset(0, iOffset)

End If
VGetOutp = Outcome
Else
VGetOutp = False
End If

End Function``````

#### Mister H

##### Well-known Member
THANKS for your help. I definitely do not mind using macros however I am uncertain on how to implement your code. I have used many codes in the past but not many Functions and I am not sure on how to manipulate your code so that it will work for me. I am wondering if I can possibly email you a sample of my workbook so you can have a peek and see if you think your code would work for my situation. I am going to send you a Private Message.

Take Care,
Mark</SPAN>

#### Peter_SSs

##### MrExcel MVP, Moderator
I am wondering if I can possibly email you a sample of my workbook so you can have a peek and see if you think your code would work for my situation. I am going to send you a Private Message.
Mark/sijpie

We prefer that to be very much a "last resort". Being a public forum we like to keep as much as possible in the public arena. Relevant references are 18 and 19 of the Posting Guidelines and #7 of the Forum Rules.

As mentioned in point 18 referred to above, as soon as any information is passed off-forum other potential helpers will likely lose interest. For example, I have been following this thread and may have alternative suggestions, but I had been waiting to see if more information about your actual layout was provided before possibly entering the thread. If it goes off-forum I, for one, will be out of the equation.

Posting a small sample layout screen shot of the data (dummy data will do) being looked up would help me understand better.

#### sijpie

##### Well-known Member
To use the function is very simple. Open the macro manager (Alt-F11) and in the lefthand panel you will see your worksheet. Right-click on the name and select 'Insert' 'Module'
In the white pane to the right copy / paste the function.

Now you have a new function in your spreadsheet, which you can use much the same as =SUM() or =VLOOKUP()

In this case the parameters it requires are:

=VGetOutp(Input cell, Lookup Range, Offset )

 Title Par1 Par2 Lookup Par2 Big a 6 Small 2 Small b 5 Large c 4 Medium d 3 Small e 2 Tiny f 1

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
So in cell G2 above there is this formula: =vgetoutp(F2,B2:B7,2)

#### Mister H

##### Well-known Member
Hi sijpie, THANKS for clarifying things. I have tried it out and at first attempt I am still not having any luck. I will have another coffee and give it another go

Peter: My apologies I definately was not tryng to break any forum rules. I figured that the resolution was already given I was (am) just having issues implementing it. Sorry about that

Have a GREAT day ALL,
Take Care,
Mark

#### Mister H

##### Well-known Member
Hi sijpie: I just wanted to say that I tried copying the macro and your sample data to a new spreadsheet and then put the formula =vgetoutp(F2,B2:B7,2) in cell G2 but the result of the formula is #NAME?

Is the vgetoutp suppose to run automatically or do I need to run it manually?

THANKS,
Mark

#### sijpie

##### Well-known Member
Forgot to say: it should run like any other excel function. The spreadsheet calls the macro, you don't need to do anything.

Replies
6
Views
214
Replies
17
Views
402
Replies
0
Views
217
Replies
4
Views
96
Replies
7
Views
186

1,195,994
Messages
6,012,748
Members
441,724
Latest member
Aalbid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back