Custom functions?

[JimmyDodger]

Hi,

Is there a way of making a complex formula into a custom function?

Basically I'm going to have to type out the following about 500 times.

=vlookup(A2,Data!A:B,2,false)

where A2 is going to change, this could be any cell.

I want to be able to type out a number of calculations without having to enter the full formula each time. I'm going to have to do about 500 of them and they wille ach be different

eg =custom(A2)+custom(A7)/custom(d5)
=custom(B4)/100

 

[Norie]

Jimmy

In theory you could create user defined functions using VBA.

But I would suggest that before going down that avenue you explain further.

For example, how will the cell references change?

 

[JimmyDodger]

OK, cheers for the reply.

In one sheet I have a 2 columns, A metric ID and a value.

Eg,

MetricID Value
1 24.5
3 1023.9756
67 0.01
400 23.2

On another sheet I have a selection of cells with metric IDs in them, and I want to be able to do calculations where I lookup the value of the metric ID based on those cells.

The only custom function I would want would be to lookup the metric ID and return its value.

 

[Norie]

Jim

Perhaps I'm missing something but wouldn't a VLOOKUP formula, or some other formula, suffice?

 

[JimmyDodger]

Hi Norie, it would but I'm trying to make my life easier because I'm going to have to type it out over 1000 times.

I've got around 500 calculations which specify the metricId, written out in words,

eg metric 20/metric 600*100

but they are all different.

so instead of writing out =vlookup(20,Data!A:B,2,false)/vlookup(600,Data!A:B,2,false)*100

I could type out =cust(20)/cust(600)*100

where cust() is the custom VBA function/formula

I have the Metric IDs in cells so what i really what to be able to do is
=cust(A2)/cust(A3)*100
so i can copy and paste/fill down for some calcs which are similar.

 

[Norie]

Jim

You say you have the IDs in cells?

So why not just use cell references?

I don't see why you need to type the values out.

But then again, perhaps I'm missing something.

 

[JimmyDodger]

How'd do you mean? Now I'm not getting you.

I have the cell references for the metric IDs, but I don't have the cell references for the values associated with those metrics, thats why i need to do a lookup.

 

[JimmyDodger]

i'll try and explain it another way, since i seem to be struggling here.

i want a function which takes x in, where x is a cell reference which contains a number (a metric ID)

the function should then output the result of
=vlookup(x,Data!A:B,2,false)

where the only thing which changes is x

I'm going to have to type the vlookup formula out over 1000 times, and if I could just type cust() instead, it would save me a alot of time. I cant just fill down or copy and paste, because im going to be building up different calculations of which the vlookup is only a small part.

 

[Norie]

Jim

But why can't you just use the cell reference in the VLOOKUP?

=VLOOKUP(A1, Data!A:B, 2,0)

 

[JimmyDodger]

I can, but because I'm going to have to type it out over 1000 times I was hoping i could just type =cust(A2) (or something similarly short) as a shortcut rather than =VLOOKUP(A2, Data!A:B, 2,0).

This would a) save me time, b) help prevent mistakes caused by typos, and c) make the formulas easier to check and or change.

I am going to have to type each one individualy, because it is a small part of a hundreds of different bigger formulas.