Forum Discussion
How do you reference a table column relative to the table rather than sheet?
Hi,
I'm just wondering if there's a cleaver way to reference a table column when using CHOOSECOLS(Table1,COLUMN(Table1[data])) when the table isn't in column A of the sheet without having to do COLUMN(...)-1 or refer to cell above the table that has a COLUMN() in it?
In other words, is there a native formula that returns the column/row position of a table column/row relative to the table itself?
Not to be pedantic, but doesn't this:
=CHOOSECOLS(Table1,COLUMN(Table1[data]))
return the same as:
=Table1[data]Perhaps this: =CHOOSECOLS(Table1,MATCH("Data",Table1[#Headers],0))
- ...I was thinking more along the lines of the CHOOSECOLS being part of a bigger formula and where it chooses multiple columns.
Looking for a more compact and dynamic way of choosing, say, the third column of a table if the table isn't starting in column A.- The shortest formula I can come up with to get the top-left column of a table is:
=MIN(COLUMN(Table1))
But to "choose" a set of columns from a table, perhaps something like this is efficient:
=HSTACK(Table1[Column a],Table1[Column c],Table1[Column e],Table1[Column g])
