Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Reference to specific table cell from outside the table

    This seems like it should be obvious and easy, but I can't seem to get it to work. Excel 2010.

    I have a table that is, except for one column, a copy of another table on another sheet. I can't just use advanced filter copy because it has to live update and I can't use macros to trigger the update for reasons of misplaced security fears.

    So how do I refer to an individual table celll? I can get a structured reference to refer to the column, but can't seem to figure out how to specify the row.

    References to the cell via the sheet name work fine, of course, but I want to use table-based references.

    What am I missing?

    Thanks,

    Jessica

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    With INDEX, you can give a range and you can specify a row and column for an individual cell

    OFFSET can also be used to give a cell (or range) offset from some standard.

    If you want more specifics you would need to provide more specific information

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Thanks - what I can't seem to do is use the Table_Name[Column Name] format with anything else after the ] to specify which row I mean.

    Let's say I have a table on one sheet named "Pointless_Table_Copy" with columns named:

    Mproj, MyTask, State

    A table named "Task_List_Filtered" on the other sheet has columns named:

    Master Project, SomethingUseless, Task, TheState

    I want to write a formula that fills the cells of Pointless_Table_Copy so that the first cell in column MyProj has the value of the first cell in
    Master Project, the second cell in MyProj has the same value as the second cell in Master Project, and so on down the column and across the rows.

    I can do it easily with references based on the sheet name and all, but I thought there must be a way to do it with a table reference as well.

  4. #4
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Hmm. It seems that if you there is a named range that is not a table (but used to be), the values in the first row are shown in the formula builder as though they are column names. But they are not, and don't work as though they were. In a real table things work as expected without the need to specify a row explicitly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •