Forum Discussion
Solved
Get specific cell data in Excel table
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.
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.
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.
3 Replies
- 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.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.
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.
