Share via

Absolute Structured Referencing - A Nifty Little Trick

  • Article

Hi Everyone,

One of our MVPs brought a tutorial on ExcelCampus (https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/) to my attention, and I thought it was so cool we should probably share it with everyone. When you select a column of data in a table to create a reference in your formulas, it automatically creates a structured reference for you - say the table header is Region in Table1, as shown below,

the reference would read

Table1[Region]

This is all well and good until you try to drag your formula to the right. Assuming you WANT the reference to move to the right as well. But suppose you don't... =LOOKUP($O$3,Table1[Region],Table1[Quarter1]) becomes =LOOKUP($O$3,Table1[Quarter1],Table1[Quarter2]), which really doesn't get you what you want. So here's the trick to get around that. Double the field name to make it a range like this: =LOOKUP($O$3,Table1[[Region]:[Region]],Table1[Quarter1]). Then when you copy over, you get the absolute column for Region: =LOOKUP($O$3,Table1[[Region]:[Region]],Table1[Quarter2]).

Cool, huh? Try it, you'll like it!

Comments

  • Anonymous
    January 01, 2003
    I ended up doing OFFSET(Table_owssvr[[#Headers],[CaseNumber]],1,0):[@CaseNumber]) for my table, hope this helps somebody who is researching this.

  • Anonymous
    August 09, 2013
    Thank you so much for the reference!  I hope this tip helps people and gives them one more reason to use tables.  Structured tables are an awesome tool, and still unknown to a lot of users. I've also developed a simple add-in that allows you to quickly add the absolute references to your formulas.  It will be available on my website for download soon. Thanks again! Jon Excel Campus  

  • Anonymous
    August 17, 2013
    Hi Anita, I noticed that the link does not work because there is a parentheses character ")" included at the end of the url. Thanks! Jon