Table References

Silver Contributor

So I am a big proponent for structured table references and wish we could use them in more places.  That said I was wondering if there is any absolute/relative designator comparable to the $ for sheet references.  Basically I have to remember drag vs fill to get what I want according to:


So if I WANT the columns to move/shift then I drag the bottom-right fill dot.  If I do NOT want the column references to change then I use fill right or copy and paste.  And if I want t a combination of staying the same and moving I'm SOL.  

So does anyone know if there is something I'm missing or some trick I can use?  For example:

=IF(Table1[Date]<TODAY(), Table1[Column10], 0)

and I want to apply that to Columns 10-20 and have each refer back to the Date column (i.e. drag right to autoincrement columns but keep [Date] fixed/absolute. 

3 Replies

OK after writing all that I had an idea and it does work!!  I would still love any other option if anyone knows any.  But using Table1[[Id]:[Id]] does force it to be 'absolute' and not shift:

drag right6868=COUNTA(Table1[[PrimaryDI]:[PrimaryDI]],Table1[PrimaryDI])=COUNTA(Table1[[PrimaryDI]:[PrimaryDI]],Table1[publicDeviceRecordKey])
fill right ctrl+R6868=COUNTA(Table1[[PrimaryDI]:[PrimaryDI]],Table1[PrimaryDI])=COUNTA(Table1[[PrimaryDI]:[PrimaryDI]],Table1[PrimaryDI])


so in my example above it would be:

=IF(Table1[[Date]:[Date]]<TODAY(), Table1[Column10], 0)

and drag right would change Column10 to 11, 12, ... but [Date]:[Date] would not change

This is why I opt for defined names or even explicit references because I do not like having to do this! If F4 worked in this context it would be a welcomed innovation.

yes F4 would be great and/or addition of the $ into the structured reference. I also like defined names because they usually have more clarity. But defined Names would not help in the above case to facilitate the filling of similar functions applied over a range of columns and Dynamic Arrays don't work inside a table