
Active Member

May 10, 2023

Hi,
I have a timesheet where I currently have to update the formula in columns J & K each week to reference the columns that I want to total up.
I would like to be able to use the named cells "StartDate" and "EndDate" (cells E7 & E8) in the formulas in columns J&K so that it is dynamic.
I have tried a few formulas to pick up the date but I cannot get it to work. I am sure I am missing something simple.
The attached sheet show a couple of example rows which hopefully make the issue clear.
Many thank in advance for your assistance.
Steve


Trusted Members
Moderators
Power BI

January 31, 2022

The problem is that the dates in the header row of a structured table are in fact text whereas the Start and End dates in E7 and E8 are real dates. So you need to create a text representation of those real dates (F7 and F8 in the attached file) first and then use INDIRECT to concatenate a dynamic reference to the correct columns in the Projects table. The formula in J will then look like this:
=IF([@[Billable?]]="N",SUM(INDIRECT("TableProjects5[@[" & $F$7 & "]:[" & $F$8 & "]]")),0)
And in K it would be:
=IF([@[Billable?]]="Y",SUM(INDIRECT("TableProjects5[@[" & $F$7 & "]:[" & $F$8 & "]]")),0)
See attached.

Answers Post


October 17, 2018

I struggled many times with columns where I wanted dates in tables.
The solution that helped me was to leave the new column as default of as text as you have but the row above the neader to dor the actual data as value and use the headerrowrange.cells(1,cellcolumn).Offset(-1,0) as value
I get errors updating links to an external file (common mistake of posted attachments) Timesheets2023
Hope my explanation makes sense
1 Guest(s)
