Log In
Register
Home
Active Member
May 10, 2023
Offline
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
See Answer
October 17, 2018
Offline
How are you referencing the named ranges in your formula?
Active Member
May 10, 2023
Offline
Hi,
Obviously the file upload failed at first.
Hopefully this clears it up.
Kind regards
Steve
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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
Offline
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
Active Member
May 10, 2023
Offline
Hi Riny,
That works - thank you! I had been trying to use indirect but was encountering an error in my syntax. The converting the real date to text was the step I was missing.
Kind regards
Steve
1 Guest(s)
