Cell Value in Custom Function

[SDowse]

Hi,

I have created a number of custom functions in Excel, see example below:

=BarclaycardPlanA(Value,Transaction)


The Barclaycard element of the function may change as we have 3 suppliers, ad the PlanA element may change because there is up to 3 plans (PlanA, PlanB and PlanC). Each function I have created is essential SupplierPlan(Value, Transaction).

In my spreadsheet, I have:
C18 = Supplier
C19 = Plan
D22 = CONCATENATE of C18 and C19

What I am trying to do is call the relevant function dependant on what is in C18 and C19. So far in D23 I have got:
=INDIRECT(D22&"("&"Value"&","&"Transaction"&")")

But this returns a #REF ! error. I've searched a bit on the web, but cannot seem to work out what the formula in D23 should be.

Any ideas?

Thanks in advance!

 

[RoryA]

You can't use INDIRECT to call a UDF.

You'd need to create a helper function that you can pass the Supplier and Plan names to as additional arguments (as well as Value and Transaction) and have that call the relevant function using Run, which takes a function name so you can build that up from the inputs.

 

[SDowse]

You can't use INDIRECT to call a UDF.

You'd need to create a helper function that you can pass the Supplier and Plan names to as additional arguments (as well as Value and Transaction) and have that call the relevant function using Run, which takes a function name so you can build that up from the inputs.

Ok, this makes more sense then. Do you have any example code that might be used in a helper function to pass supplier and plan names (as well as Value and Transaction, and how to use Run?

 

[RoryA]

Basically something like this:

Function HelperFunction(Supplier, Plan, Value, Transaction)
HelperFunction = application.run(Supplier & Plan, Value, Transaction)
End Function

 

[SDowse]

Basically something like this:

Function HelperFunction(Supplier, Plan, Value, Transaction)
HelperFunction = application.run(Supplier & Plan, Value, Transaction)
End Function

Thanks again for your help, and apologies if I am asking stupid questions...

I assume in my worksheet I would enter =HelperFunction(Value,Transaction)? If so, it is returning a #Value ! error...is there anything I need to do in order to make this work? Any pre-cursors or anything?

Thanks again!

 

[RoryA]

No you need to pass 4 arguments not two. The first two are the supplier and plan names.

 

[SDowse]

No you need to pass 4 arguments not two. The first two are the supplier and plan names.

Perfect...many thanks. I'm a massive novice at this sort of thing.

 

[RoryA]

Glad to help. We were all beginners once!