Reference to specific table cell from outside the table

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote