Forum Discussion

mtarler's avatar
mtarler
Silver Contributor
Jun 26, 2023

Table References

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. 

excel

3 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 26, 2023

    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])
    copy-paste6868=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

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jun 26, 2023

      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.

      • mtarler's avatar
        mtarler
        Silver Contributor
        Jun 26, 2023
        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

Resources