• 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.

Lookup result is a string to be converted to a formula

David Evans

David Evans

Active Member
Following on from my Friday afternoon challenge for the bored, I'd like to be able to evaluate the string in cell E11 as a formula in cell B2 - the result of =VLOOKUP(FALSE,$B$8:$E$13,COLUMNS($B$8:$E$13),FALSE)

Is this a job for Evaluate or is there a better way?

Many thanks for your insight and patience with my slow learning pace ...o_Oo_O
 

Attachments

Yes

If you setup a named formula as:
MyFormula =Evaluate($B$2)

Then on the worksheet simply use: =MyFormula

See attached file with revised formula
 
Thanks Hui - appreciate your quick response. Congrats to the Wallabies for beating the All Blacks, btw. No Ashes conversations, of course .....

Am working on building your thoughts into this rather large workbook I have here ...
 
Ha ha
I don't even know how to spell Rugby?
Well that's about all I know about it.

I am a staunch Hawthorn supporter in Aussie Rules Football
upload_2015-8-10_11-23-41.png
 
Hui (& Narayan if you're watching)

This is a combination of the questions I've been posting over recent days - I think!
I've tried to simplify the elements of a rather complicated legacy workbook - it has been built over a period of 10 years and had/still has some very simplistic ideas.
Most of the work on these files was entirely manual, so by nature the work was slow and inaccurate as it relied on a lot of user input. My project has been to integrate the work book with some Access dbs that hold data and formulas for calculations (Fee IDs)

My current challenge is to automate the process reflected in the Rows 13 & Rows 43-45 of the worksheet Addl Fees.

For each account I want to return the appropriate formula to calculate the Amount of the Mutual Funds that are to be subject to Service Fees. The formula appears as text in H43:H45 and is triggered by a FALSE in the corresponding column for that account. The Mutual Fee data comes from the worksheet MF Exp.

I've greatly simplified things in here to make it easy to follow - the real model relies on lots of GETPIVOTDATA and is considerably larger than my example - however the principles are very similar.

The results I expect to get in Account 1 are 711,507 less the amounts in Fund 6 and Fund 9 being (48,000 + 27,895) = 635,162.

Account 2 would be 828,166 as it is simply the sum of all the MF holdings.
The challenge I've been having is getting the text formula to be relative to each account ....

Does this make any sense to you folks? It has me completely discombobulated ...
 

Attachments

Last edited:
Hi David ,

I think your expected results are wrong ; if I have understood your requirement , the value in C13 is to come through an evaluation of the formula in H43 ; the value in D13 is to come through an evaluation of the formula in H44 , and so on ; is this correct ?

Narayan
 
NARAYANK991 said:
Hi David ,

I think your expected results are wrong ; if I have understood your requirement , the value in C13 is to come through an evaluation of the formula in H43 ; the value in D13 is to come through an evaluation of the formula in H44 , and so on ; is this correct ?

Narayan
Click to expand...

Hi Narayan - Thanks for responding - sorry for the delay, I went for a game of squash!

Let me see if I can clarify the question for you and me!
The value in C13 is a little different from other cells surrounding it in that I need to evaluate the formula in the range H43:H45 that corresponds to the FALSE in C43:C45

Essentially, in C13 the value should be MF Exp E14-E12-E9 - SO YOU'RE CORRECT! The text string I have in H45 should be
=INDEX('MF Exp'!$E$14:$N$14,,2*COLUMN('Addl Fees'!A$1)-1)-(INDEX('MF Exp'!$E$9:$N$9,,2*COLUMN('Addl Fees'!A$1)-1)+INDEX('MF Exp'!$E$12:$N$12,,2*COLUMN('Addl Fees'!A$1)-1))

I had confused my Row Nos with my Fund Nos!
 
Hi David ,

Still not clear ! Let me put it down in my own words.

The result in C13 - since C45 is FALSE , it means that the result in C13 will be based on the evaluation of the formula in H45.

The result in D13 - since D43 is FALSE , it means that the result in D13 will be based on the evaluation of the formula in H43.

The result in E13 - since E45 is FALSE , it means that the result in E13 will be based on the evaluation of the formula in H45.

The result in F13 - since F43 is FALSE , it means that the result in F13 will be based on the evaluation of the formula in H43.

Is the above correct ?

Narayan
 
Last edited:
Narayan -

Appreciate your patience with this - it is rather confusing. The results in C13:G13 are evaluated on the range C43:G45 - A FALSE in any column triggers the the formula in the corresponding row.
For example in C13 the False is in C45 which means I want to use the formula in H45.
For D13 the FALSE is in D43 so we would use the Formula in H43.

Not all asset types have multiple handling methods, but alas Mutual Funds have many! Trustee fees are similar, but if i can solve the Mutual Fund problem, the same principles will apply to the Trustee Fees.
 
Really appreciate your help, Narayan.
You are nearly there - have modified the worksheet slightly to better highlight the cells that trigger the calculation ...

What I'm not understanding is how you are getting the correct cell to evaluate the formula in C13:G13 (well G13 evaluates to nothing! as it's an empty account)

Thanks again - I am off to bed as I have to catch an early train to Los Angeles in the morning. Woot Woot!
 

Attachments

Thanks Narayan - as usual you have provided a very elegant solution and I have learned something new from you. I'm not certain I understand you defined the Eval Formula range, but will see if I get it as I build it into the main model.
 
I'm not quite understanding where you create the Range Formula name Eval. It appears to me that it is 7 columns to the right of where you want it to evaluate, but I'm clutching at straws!
I arrived at that by selecting a cell and then editing the range Manager for Ecal to see its reference .... am I on the right track?o_O

Edit - I finally worked it all out - Thanks again Narayan - this is a very interesting "function"
 
Last edited:
You must log in or register to reply here.
Back
Top