VLOOKUP and range name formula

The Horse

Board Regular
Joined
Feb 20, 2004
Messages
68
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
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)
 
Upvote 0
You can't directly evaluate a formula that is text. What does your formula look like in any case?
 
Upvote 0
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
 
Upvote 0
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 ! ...

... 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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,552
Messages
6,179,487
Members
452,917
Latest member
MrsMSalt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top