Forum Discussion
Master list of formulas to use in tables
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?
- 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?
- 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.- 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?
