Jump to content


Is is possible to lock a cell reference when using tables?

structured table reference absolute reference

  • Please log in to reply
2 replies to this topic

#1 phoenixrizing


    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 16 September 2015 - 05:14 PM

I know you can lock a cell reference by using $ where needed (e.g. $D$6, M$7, and so on).


But what if you are using a table and want to lock a reference?


In the attached file, I show an example of referencing the first column in a table. As is often the case, I want to copy/drag that formula to other cells, but also want the reference to be static.


Since I'm using a table, thus using names in that table, they shift as I copy/drag the formula over.


If there's no way to lock a name like this, I'm guessing that the solution would be to simply type in the array reference?


Attached File  Locking references.xlsx   13.35KB   207 downloads

#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 16 September 2015 - 09:17 PM

Hi phoenixrizing,


Although there isn't a way to define Structured Table References as absolute references, you can prevent them from incrementing by copying and pasting them instead of using AutoFill. This isn't usually an ideal solution, but it's useful to be aware of the difference in behaviour when copying and pasting instead of autofilling.


Absolute references can only be created using standard cell references, so the solution in your case would be to replace the structured reference Table2[Review date] with the cell reference D$2. Of course, the second part of this reference will only increment if you are autofilling downwards instead of across, so you may still find that you need to do some manual editing to achieve the result you need.


You can find a full explanation of absolute and relative cell references in the video lesson: Essential Skills Lesson 3-12 Understand absolute and relative cell references.

You can also find an explanation of structured references like Table2[Review date] in the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.


I hope this is what you were looking for, but as always please feel free to reply if you have any more questions.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#3 phoenixrizing


    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 17 September 2015 - 02:53 PM

Thanks, Jonathan.

1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users