• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP and Named Formula

O

OliverCrispin

New Member
Hello,


I have a vlookup calculation, the result of which returns the name of a named formula. What I want the vlookup calculation to do is to compute the named formula rather than return the name of the named formula as a text string.


How do I do that?


Many thanks
 
Hi Crispin ,


Wrap an INDIRECT around it.


For example , suppose I have a cell C1 , which contains the text ABC ; now suppose I have a range B4:C7 , where the first column B contains ABC somewhere in the range B4:B7 ; I return the corresponding value in column C , by using the formula :


=VLOOKUP(C1,B4:C7,2,False)


I may get a text string MASTER1.


Now suppose I have defined a named range MASTER1 as referring to cell $D$4 , and D4 contains the value 500.


If I wrap an INDIRECT around the VLOOKUP formula above , by :


=INDIRECT(VLOOKUP(C1,B4:C7,2,False))


I will get 500 as the result.


Narayan
 
Apologies I should have thought about that.

http://db.tt/LknCLXGv

I hope this works.

I think the yellow cells show what I want to do.

I am trying to get the yellow cell in column G to perform the same way as the one in column H

Many thanks

Oliver
 
Narayan,

Thanks for the response.

I did try but is does not seem to work with mine. In my case "MASTER1" (using your terms) refers to a defined calculation rather than a cell.

Thanks

Oliver
 
Hi Oliver ,


You first need to tell us what nc_Metric1 , nc_Metric2 and nc_Metric3 are supposed to refer to , since I do not know what your formula is supposed to return ; at present it returns an error value.


nc_Metric4 , nc_Metric5 and nc_Metric6 are dependent on the above 3 ; first we need to rectify the problem in them , before we can come to nc_Metric6.


Narayan
 
Narayan,


Sorry, I could have been clearer.


"nc" in my notation refers to "named calculation"

nc_Metric1, nc_Metric2 and nc_Metric2 are defined calculations and the formulae are shown in the shaded table on the right.


They are, if you like, halfway houses in the calculations. So, for example, Metric 4 is calculated by taking Metric 3 and subtracting Metric 2 from it.


I have structured things this way because I want to achieve flexibility as the data expands and also to show the steps in the process.


The formula for nc_Metric6 works fine when it is directly entered with "=nc_Metric6" (column H) but not when what appears to me as a human to be the same thing by calculated in column G.


I hope that helps!


Many thanks
 
Hi Oliver ,


I differ ; I put =nc_Metric1 , =nc_Metric2 , =nc_Metric3 , and so on in cells in the worksheet , and all of them , from 1 through 6 , are giving error results ( #VALUE! ).


When I try to evaluate any of them , they give the same results.


If you can explain what you want nc_Metric1 , nc_Metric2 and nc_Metric3 to refer to , in plain words , I can probably understand more easily than trying to understand the formulae you have used to define them.


Narayan
 
@NARAYANK991

Hi!


As far as I could understand when I read the original post and checked the uploaded file, OliverCrispin is trying to do the same that INDIRECT function does with a cell argument (i.e., retrieving the content of that cell address) but with named formulas.

That's to say, like =INDIRECT("B23") retrieves a 10 if B23 contains "A15" and A15 contains a 10, he wants to retrieve the equivalent of =INDIRECT(VLOOKUP(C1,B4:C7,2,False)) where VLOOKUP(C1,B4:C7,2,False) returns a string with the name of named formula.


I didn't post because I have no clue of how to do it, if it could be done. It sounds like a dynamic evaluation of a string that contains a named formula.


Regards!
 
Hi Oliver,

There was a similar question a few months ago on a different forum... the poster finally went with a VBA solution:

http://stackoverflow.com/questions/14856889/referencing-dynamic-named-range-in-excel-formula


Apparently, INDIRECT does not work with Named formulas like the following:

A1 refers to "Master1"

Master1 refers to "=SubCalc1 + SubCalc2"

where SubCalc1 and SubCalc2 are in turn Named formulas or references.

so... INDIRECT(A1) returns an error


There might be some tricks you can use with the old Excel4 Evaluate macro, but a better approach might be to redesign your solution.


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top