VLOOKUP and range name formula

The Horse said:

I can't quite crack this...posts on the web seem contradictory:

I've created a group of formulae and named each one - it's for a cost allocation model.
Then, I've done a table with the approaches in column A and the range names in column B:

A B

Approach 1 Name 1
Approach 2 Name 2

When I've referenced them using vlookup(item,a1:b2,2,0) I can get Name 1 as text, but can't get it to evaluate to the formula which I've stored as Name 1.

In other words, I want the lookup to find the relevant range name and evaluate to the formula embedded in the range name.

Tried vlookup(item,indirect(a1:b2),2,0) but it doesn't work.
Anyone know the answer ?

Thanks

Mick the Horse

Click to expand...

If the formula expressions are admissible (i.e., they can be instantiated), add the following code to your workbook...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
' Charles Williams aka FastExcel
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
_________________________________________________

Now you can invoke something like:

=EVAL(Item,A1:B2,2,0)

You can't directly evaluate a formula that is text. What does your formula look like in any case?

Glenn

e.g.

=(Staff!$AW$3:$AW$105<>"x")*(Staff!$B15=Staff!W$2)*(SUMPRODUCT((Staff!$B15:$B15=Staff!W$2)*(Staff!$E15:$E15="Yes")*(Staff!$S15:$T15)*(Staff!$AW15:$AW15<>"x"))/SUMPRODUCT((Staff!$B$3:$B$105=Staff!W$2)*(Staff!$E$3:$E$105="Yes")*(Staff!$S$3:$T$105)*(Staff!$AW$3:$AW$105<>"x")))

Hence the need to use a range name formula instead !

So, you're saying you can't look up a table and get an answer such as "My_Range" and evaluate that as a range name formula (as in the above !!) ?

Aladin - not sure I understood your reply - sorry !

Mick the Horse

The Horse said:

Glenn

e.g.

=(Staff!$AW$3:$AW$105<>"x")*(Staff!$B15=Staff!W$2)*(SUMPRODUCT((Staff!$B15:$B15=Staff!W$2)*(Staff!$E15:$E15="Yes")*(Staff!$S15:$T15)*(Staff!$AW15:$AW15<>"x"))/SUMPRODUCT((Staff!$B$3:$B$105=Staff!W$2)*(Staff!$E$3:$E$105="Yes")*(Staff!$S$3:$T$105)*(Staff!$AW$3:$AW$105<>"x")))

Hence the need to use a range name formula instead ! ...

Click to expand...

... you'll have to explain why this has to be a text string. And explain why this can't be a regular formula that evaluates normally, and the result is transferred via range names if range names are what you want to use.

Glenn,

what I'm looking to do seems (!!) straightforward:the answer's obviously not !

e.g. If I have two named ranges:

Name1 is sum(A:C)
Name2 is sum(D:E)

And a table with

Column A Column B
Approach 1 Name1 (as text in column B)
Approach 2 Name2 (again as text..etc)

if I reference the above with vlookup(z1,a1:b2,2,0) - where z1 contains Approach 1 as text
it will evaluate to "Name1" as text - whereas I want it to add columns A to C by actioning the formula in the range name (or add D to E if I change it to Approach 2).

The real forumulae are quite complex, as it's a cost allocation model - what I want is the ability to change the apportionment basis (through changing the "approach").

Hope you understand !!

Thanks

Mick the Horse

You are using the term "named ranges" ... are they actually ranges?

Does "sum(A:C)" exist in a cell, or does it exist only in a defined name.

The Horse said:

Glenn,

what I'm looking to do seems (!!) straightforward:the answer's obviously not !

e.g. If I have two named ranges:

Name1 is sum(A:C)
Name2 is sum(D:E)

And a table with

Column A Column B
Approach 1 Name1 (as text in column B)
Approach 2 Name2 (again as text..etc)

if I reference the above with vlookup(z1,a1:b2,2,0) - where z1 contains Approach 1 as text
it will evaluate to "Name1" as text - whereas I want it to add columns A to C by actioning the formula in the range name (or add D to E if I change it to Approach 2).

The real forumulae are quite complex, as it's a cost allocation model - what I want is the ability to change the apportionment basis (through changing the "approach").

Hope you understand !!

Thanks

Mick the Horse

Click to expand...

Set up the table of expressions on, say, Admin.

Name TEX this table housing the approaches and expressions, consisting of...

SUM(A:C)
SUM(D:E)

Sheet1

F2: Approach 1

G2:

=EVAL(VLOOKUP(F2,TEX,2,0))

In G2, you will find a result calculated according an expression retrieved from the table of expressions. However, it must be noted that such usage is of a limited value for EXCEL is not a programming language like say LISP.

In the example, I've defined sum(a:c) as a range, so that I can use it as a named formula, like any Excel function.

Aladin,

that is a brilliant solution !!!!!!!!!!!!!
Thank you so much !

Regards

Mick the Horse