Home

Need to call from a list of formulas and execute once called

%3CLINGO-SUB%20id%3D%22lingo-sub-925712%22%20slang%3D%22en-US%22%3ENeed%20to%20call%20from%20a%20list%20of%20formulas%20and%20execute%20once%20called%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-925712%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20be%20able%20to%20call%20a%20formula%20based%20on%20the%20account%20name%20that%20gets%20entered%20into%20a%20cell.%26nbsp%3B%20I%20have%20the%20list%20of%20formulas%20created%20as%20text%20(no%20equal%20sign%20at%20the%20beginning)%20named%20as%20formulas%20and%20I%20thought%20the%20Indirect%20function%20would%20work%20to%20call%20the%20text%20and%20then%20execute%20it%20as%20a%20formula%2C%20but%20it%20only%20displayed%20the%20text%20for%20the%20formula.%26nbsp%3B%20I%20need%20to%20calculate%20the%20cost%20based%20on%20data%20in%20other%20cells%2C%20but%20won't%20know%20which%20formula%20to%20apply%20until%20the%20account%20name%20is%20entered.%26nbsp%3B%20The%20cells%20I%20need%20to%20reference%20stay%20the%20same%2C%20but%20cost%20rate%20I%20apply%20varies%20by%20account.%26nbsp%3B%20How%20can%20I%20dynamically%20choose%20the%20correct%20formula%20that%20then%20executes%20when%20called%20instead%20of%20executing%20in%20the%20list%20of%20formulas%3F%26nbsp%3B%20I'm%20familiar%20with%20VLOOKUP%20and%20Data%20Validation%2C%20but%20can't%20figure%20out%20how%20to%20do%20this.%26nbsp%3B%20An%20example%20of%20the%20formula%20I'm%20using%20is%20this%20M6*(H6*.3%2BI6%2BX)%20where%20the%20%22.3%22%20will%20vary%20and%20the%20%22X%22%20will%20vary%2C%20but%20the%20cell%20references%20stay%20the%20same.%26nbsp%3B%20Any%20thoughts%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-925712%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-925988%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20call%20from%20a%20list%20of%20formulas%20and%20execute%20once%20called%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-925988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430568%22%20target%3D%22_blank%22%3E%40MikeSmith2414%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20workbook%20that%20contains%20three%20possible%20answers%20to%20your%20question.%20And%20there%20are%20probably%20more%20ways%20to%20achieve%20the%20same.%20Not%20sure%2C%20though%2C%20how%20your%20schedule%20looks%20like%20and%20which%20solution%20suits%20you%20best.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-927187%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20call%20from%20a%20list%20of%20formulas%20and%20execute%20once%20called%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-927187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BThank%20you!%26nbsp%3B%20I%20think%20I%20can%20make%20it%20work%20from%20one%20of%20these!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
MikeSmith2414
New Contributor

I need to be able to call a formula based on the account name that gets entered into a cell.  I have the list of formulas created as text (no equal sign at the beginning) named as formulas and I thought the Indirect function would work to call the text and then execute it as a formula, but it only displayed the text for the formula.  I need to calculate the cost based on data in other cells, but won't know which formula to apply until the account name is entered.  The cells I need to reference stay the same, but cost rate I apply varies by account.  How can I dynamically choose the correct formula that then executes when called instead of executing in the list of formulas?  I'm familiar with VLOOKUP and Data Validation, but can't figure out how to do this.  An example of the formula I'm using is this M6*(H6*.3+I6+X) where the ".3" will vary and the "X" will vary, but the cell references stay the same.  Any thoughts?

2 Replies

@MikeSmith2414 

I have attached a workbook that contains three possible answers to your question. And there are probably more ways to achieve the same. Not sure, though, how your schedule looks like and which solution suits you best.

 

 

@Riny_van_Eekelen   Thank you!  I think I can make it work from one of these!

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies