SOLVED

Get specific cell data in Excel table

Copper Contributor
Hi
Can someone please tell me the syntax for getting a specific cell value in a table in another sheet (but still the same workbook)?
The reason I want to do this is to improve on existing excel workbooks that doesn’t use tables to organize data and I would like to avoid rebuilding them entirely. Some sheets have referenced other sheets cells directly and I would like to do that but use tables instead.
3 Replies

@Niklas_E 

If that's another table the reference will be the same as for the cell in range, e.g. = Sheet2!B22.

It's always better to lookup or use INDEX() but it very depends on how your data is structured.

Thanks Sergei, but I was hoping to address it the way you address tables if that is possible. I mean something in the line of = TABLE_NAME(…….. if that makes sense.
The reason for it is that I want to make the changes so that if I insert, or remove, a row it will automatically update and insert or remove a row the same way.
best response confirmed by Niklas_E (Copper Contributor)
Solution

@Niklas_E 

If you enter formula =Table[@[field]] in some cell of row 15 in Sheet1 , and Table is in Sheet2, it returns field value from row 15 in Sheet2 if it is within the table, otherwise error.

 

Alternatively you may use something like

=INDEX(Table1[field], ROW() -ROW(title) )

to use relative position within the table.

1 best response

Accepted Solutions
best response confirmed by Niklas_E (Copper Contributor)
Solution

@Niklas_E 

If you enter formula =Table[@[field]] in some cell of row 15 in Sheet1 , and Table is in Sheet2, it returns field value from row 15 in Sheet2 if it is within the table, otherwise error.

 

Alternatively you may use something like

=INDEX(Table1[field], ROW() -ROW(title) )

to use relative position within the table.

View solution in original post