Excel: Reference to Table Range? 1

[cutestuff]

hi there,

For some reason I cannot get this code to work no matter what I tried.
I have a table called tblTracker in excel with the columns Name, Start Date and End Date.

The table starts on Row 6 (Row 5 are the headers). Column D is the Start Date Column and Column E is the End Date Column. Column B is the Name column.

I am trying to find duplicate / overlapping date ranges.

This code works:
SUMPRODUCT(($D6<=$E$6:$E$60)*($E6>=$D$6:$D$60)*($B6=$B$6:$B$60))>1

But when I try to reference the table columns (so it is dynamic), I can't get it to work.

I have tried:
SUMPRODUCT(($D6<=tblTracker[End Date])*($E6>=tblTracker[Start Date])*($B6= tblTracker[Name]))>1

And other things but it keeps giving me a formula error.

Please help and thanks!
Jen

 

[SkipVought]

Hi,

And you used the built-in intelesense to complete the table elements using the TAB key?

It appears to be a good formula as long as the element names are correct.

 

[cutestuff]

I am almost certain the table names are correct as I have been using them on formulas everywhere else on the spreadsheet.
I am trying to enter this formula in the Conditional Formatting section though - does that make a difference?

And thanks for the help!

 

[cutestuff]

Sorry - found out it was because of conditional formatting.
I had to enclose the table names in quotation marks.

My brain wasn't functioning well working late yesterday...

Thanks for the help though.

 

[SkipVought]

Always ALWAYS use the intelligent selection feature to complete names!!