One workaround demonstrated in the article is to use the INDIRECT function each time you reference an Excel Table, I will now explain the formula in great detail.
Explaining Data Validation formula in cell C5
A great feature with Excel Tables is that a reference pointing to an Excel Table doesn't change (unless you change the Excel Table name or headers) even if the Table grows or shrinks, the reference stays the same.
If you want to see each calculation step using the Evaluate Formula tool then copy the formula and paste to any empty cell not adjacent to the Excel Table, we will delete it later on.
Go to tab "Formula" on the ribbon and click the "Evaluate Formula" button, this opens a dialog box that allows you to see each step in the calculation.
Simply click the Evaluate button located on the dialog box to go to next step, keep clicking to see all calculation steps.
Step 1 - Check if start date is smaller than or equal to the end dates
Table1[@Start] is a reference to a cell on the same row as the selected cell and in column Start in Table1.
The less than sign and the equal sign combined checks if the value in the cell described above is less than or equal to the value in column End in Table1.
The less than and equal sign are logical operators and return a boolean value TRUE or FALSE. Table1[@Start] is a reference to a cell in column Start that is located on the same row as the current cell, in this case, cell C5. The @ character in Table1[@Start] means it is on the same row as the selected cell.
An Excel date is actually a number formatted as a date, 1/1/1900 is 1 and 1/1/2000 is 36526. There are 36525 days between 1/1/1900 and 1/1/2000.
Time is a fraction of a day, one hour is 1/24 and is approx. 0.041667 and 24 hours is 1. 12:00 PM is 0.5.
The parentheses let you control the order of calculations, the asterisk multiplies the arrays which means that we apply AND logic meaning both values on the same row must return TRUE in order to return TRUE.
When you multiply boolean values Excel converts the output to their numerical equivalents, TRUE = 1 and FALSE = 0 (zero).