Master list of formulas to use in tables

[ITTom365]

Hello, 

I have an Excel workbook with multiple tabs all containing a table with the same headers (i.e. the structure and header name of each table on each tab is identicle). 

On another sheet I have a series of 'master formulas' stored as text that users copy and paste into a column of one of the tables. The formulas are in the following table header format: 

'=IF([@[Long]]="";"";IFERROR(((([@[Long]/100 )* INDEX(OP_TAB[Speed];EQUIV([@[Type operation]];OP_TAB[Op];0)))+(INDEX(OP_TAB[Time];EQUIV([@[Type operation]];OP_TAB[Op];0))));""))

EQUIV = MATCH (working in a French environment 

i.e. copying the formulas into a table works find (excel finds the headers and everything works) 

I want the user to be able to automatically enter a formula based on a look up. 

I have tried index match and the formula populates the field but is not evaluated (is text instead) 

I have tried a custom EVALUATE function in VBA but always get a #VALUE error 

Can anyone help?

[#2]

Which version of Excel are you using exactly? Perhaps this is something that can easily be "solved" using the new LAMBDA function: https://jkp-ads.com/Articles/Excel-lambda-function-basics.asp

If you name your LAMBDA functions smartly, like starting them all with "tblFun.", the user can be instructed to type =tblfun to get a list of available table formulas.

[#3]

Hello, Im using 2016. I started reading about Lambda functions yesterday, I will read the info you have linked to - thanks

My question immediately however, is: If I use header names in a generic formula, does Excel evaluate them related to the table they are in? I created a test table yesterday and put the formula into a named range. The formula can be selected in a different table but only calculates using the data in the original table.

[#4 · Accepted solution]

I'm afraid you can't do that, range names cannot contain table headings without the table name and hence will always point to the same table. If I may ask: why do you want to have these "standard" formulas, can't you just copy an otherwise empty table which has the column formulas already in place?

[#5]

Hi Thanks for the input: I was affraid that was the answer!

Our office staff construct job specifications with many discreet steps, however any step will fall under a catagory with a fixed method of calculation. Hence I want them to be able to select a catagory from a validation list and a look up selects the correct formula. The suggestion you made 'sort of works' except each job structure is highly specific so I can not create a standard row structure only a standard column structure, I have no idea on what row a formula needs to apply (I hope that makes sense).

Thanks for the input and the information on the Lambda function, this evening bed time reading

[#6]

You could consider writing a complicated formula like this one:
=IFS([@Status]="Promote",CalcuateNewSalary,[@Status]="New Hire",CalculateSalary,[@Status]="...",...)