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

 

[sijpie]

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]

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.

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]

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.

THANKS Again for your suggestion.

Take Care,
Mark</SPAN>

 

[Peter_SSs]

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]

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]

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]

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]

Mark, did you put the macro in a module in VBA editor?

see here some guidance. Also look at section of macro security in the first link. Perhaps your security is set to not run macros
Excel VBA Tutorial - Easy Excel Macros
Excel VBA Basic Tutorial 1
Excel Macro Writing Mini-Tutorial

 

[sijpie]

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