Forum Discussion
Solved
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'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.
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?
- 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 🙂
