Use column title in a formula

1

I don’t know if this is possible, or if there is a work around.

I have 12 columns (one for each month)
in each column I have an extensive formula calculation in which I need te refer to the month number.
Now I do this manually, but when I change the formula (doc still in building status) i have to think about the reference.

my current formula
If(thisRow.[end date]< Date(year,2 ,1 ),"" ,[Salery lines].filter(Employee=thisRow.employee and[salery date]<= EndOfMonth(Date(year,2,1),0)).jaarkost.last() )

and I need to fill the 2 for February
the formula is the same in each column, except for the month
I would love to name the column “2” and then refer the column name (this column.name)
Is this possible, or do I need to post this in suggustions

I need it in columns to be able to copy paste the data in a google sheet

Pieter

2

Yep - I understand the importance of this requirement - it would provide a logical interpolated formula that makes your solution much more elegant and more esily maintained. However, I’m not sure how to address it.

Ideally, you need thisCol to return an ordinal number, right?

I hate hacky workarounds but one that comes to mind is a hidden row that contains the month numbers. Another is literally a data dictionary table in the document.

Thinking like @Paul_Danyliuk does, he might say -

It’s simple, pivot the table 90 degrees and now your columns are rows and you can perform the dependent computations effortlessly. Rendering the results is achieved by the choice of a theme that displays months as columns.

That’s all I’ve got.

3

I have two ways to solve this problem:

  1. By using switch-operation. For example:
switch([Month].ToText(),
    "January",   thisRow.[January],
    "February",  thisRow.[February],
    "March",     thisRow.[March],
    "April",     thisRow.[April],
    "May",       thisRow.[May],
    "June",      thisRow.[June],
    "July",      thisRow.[July],
    "August",    thisRow.[August],
    "September", thisRow.[September],
    "October",   thisRow.[October],
    "November",  thisRow.[November],
    "December",  thisRow.[December] )
  1. By making 1D-table instead of 2D-table. You can put all 12 columns in 1 column with creating 12 month-rows for every year. And after that you can apply “Group” menu in table properties and select "Group by Month " + “Top” option. After that you can work with your 1D-table as if it were 2D-table.
1 Like
4

Yep that’s pretty clean @actualinfo!

I’m trying to connect all posts like this to my suggestion here

1 Like