Forum

Notifications
Clear all

Trying to sum the row of a table between 2 dynamic columns

6 Posts
3 Users
0 Reactions
86 Views
(@stevem)
Posts: 5
Active Member
Topic starter
 

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

 
Posted : 11/05/2023 4:49 am
(@keebellah)
Posts: 373
Reputable Member
 

How are you referencing the named ranges in your formula?

 
Posted : 12/05/2023 2:04 am
(@stevem)
Posts: 5
Active Member
Topic starter
 

Hi,
Obviously the file upload failed at first.

Hopefully this clears it up.

Kind regards

Steve

 
Posted : 12/05/2023 8:19 am
Riny van Eekelen
(@riny)
Posts: 1194
Member Moderator
 

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.

 
Posted : 13/05/2023 1:05 am
(@keebellah)
Posts: 373
Reputable Member
 

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

 
Posted : 13/05/2023 2:22 am
(@stevem)
Posts: 5
Active Member
Topic starter
 

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

 
Posted : 13/05/2023 2:58 am
Share: