sccowl
New Member
Is there a way to pass a value into formula that references a table, without using INDIRECT function?
I have a table set up with multiple columns, one per employee and a row per day, for example
I currently use a formula using the structured table references like
=sumifs(emp_table[Alfie],emp_table[[Weeknum]:[Weeknum]],$A2) where A2 contains the weeknum I want to sum.
I can drag copy this formula which automatically updated the second column to Bert, third to Charlie etc and gives the correct result. (Tip - Using Ctrl+D over the range also works great)
The formula works fine, but is a little inflexible as it requires a different formula in each column as it needs to reference a specific column name. This is likely to go wrong over time if and when its maintained by someone else.
Ideally, rather than referencing the column name emp_table[Alfie] I'd rather make this dynmaic and pass in a parameter, say emp_table[B$1] where B1 contains a name like 'Alfie'. I would then be able to use one common formula across all columns, so could be confident it would always be referncing the correct data so long as the value in B1 is correct.
It is possible to do this using INDIRECT formula such as =sumifs(indirect("emp_table["&B$1&"]"),emp_table[[Weeknum][Weeknum]],$A2) , but I don't want to introduce a volatile formula into large complex sheet.
It also needs to be done in a standard sheet, I can't use any VBA.
Thanks for any advice or ideas.
I have a table set up with multiple columns, one per employee and a row per day, for example
| Date | Weeknum | Alfie | Bert | Charlie |
| 1/1/2019 | 1 | 0 | 1 | 1 |
| 2/1/2019 | 1 | 1 | 1 | 0 |
| 3/1/2019 | 1 | 1 | 1 | 1 |
I currently use a formula using the structured table references like
=sumifs(emp_table[Alfie],emp_table[[Weeknum]:[Weeknum]],$A2) where A2 contains the weeknum I want to sum.
I can drag copy this formula which automatically updated the second column to Bert, third to Charlie etc and gives the correct result. (Tip - Using Ctrl+D over the range also works great)
The formula works fine, but is a little inflexible as it requires a different formula in each column as it needs to reference a specific column name. This is likely to go wrong over time if and when its maintained by someone else.
Ideally, rather than referencing the column name emp_table[Alfie] I'd rather make this dynmaic and pass in a parameter, say emp_table[B$1] where B1 contains a name like 'Alfie'. I would then be able to use one common formula across all columns, so could be confident it would always be referncing the correct data so long as the value in B1 is correct.
It is possible to do this using INDIRECT formula such as =sumifs(indirect("emp_table["&B$1&"]"),emp_table[[Weeknum][Weeknum]],$A2) , but I don't want to introduce a volatile formula into large complex sheet.
It also needs to be done in a standard sheet, I can't use any VBA.
Thanks for any advice or ideas.
