SOLVED

Master list of formulas to use in tables

Occasional Contributor

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?

 

5 Replies
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.
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.
best response confirmed by ITTom365 (Occasional Contributor)
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?
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
You could consider writing a complicated formula like this one:
=IFS([@Status]="Promote",CalcuateNewSalary,[@Status]="New Hire",CalculateSalary,[@Status]="...",...)