SOLVED

Get specific cell data in Excel table

%3CLINGO-SUB%20id%3D%22lingo-sub-3100445%22%20slang%3D%22en-US%22%3EGet%20specific%20cell%20data%20in%20Excel%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100445%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3ECan%20someone%20please%20tell%20me%20the%20syntax%20for%20getting%20a%20specific%20cell%20value%20in%20a%20table%20in%20another%20sheet%20(but%20still%20the%20same%20workbook)%3F%3CBR%20%2F%3EThe%20reason%20I%20want%20to%20do%20this%20is%20to%20improve%20on%20existing%20excel%20workbooks%20that%20doesn%E2%80%99t%20use%20tables%20to%20organize%20data%20and%20I%20would%20like%20to%20avoid%20rebuilding%20them%20entirely.%20Some%20sheets%20have%20referenced%20other%20sheets%20cells%20directly%20and%20I%20would%20like%20to%20do%20that%20but%20use%20tables%20instead.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3100445%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3101081%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20specific%20cell%20data%20in%20Excel%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3101081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1293408%22%20target%3D%22_blank%22%3E%40Niklas_E%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20another%20table%20the%20reference%20will%20be%20the%20same%20as%20for%20the%20cell%20in%20range%2C%20e.g.%20%3D%20Sheet2!B22.%3C%2FP%3E%0A%3CP%3EIt's%20always%20better%20to%20lookup%20or%20use%20INDEX()%20but%20it%20very%20depends%20on%20how%20your%20data%20is%20structured.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3102713%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20specific%20cell%20data%20in%20Excel%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3102713%22%20slang%3D%22en-US%22%3EThanks%20Sergei%2C%20but%20I%20was%20hoping%20to%20address%20it%20the%20way%20you%20address%20tables%20if%20that%20is%20possible.%20I%20mean%20something%20in%20the%20line%20of%20%3D%20TABLE_NAME(%E2%80%A6%E2%80%A6..%20if%20that%20makes%20sense.%3CBR%20%2F%3EThe%20reason%20for%20it%20is%20that%20I%20want%20to%20make%20the%20changes%20so%20that%20if%20I%20insert%2C%20or%20remove%2C%20a%20row%20it%20will%20automatically%20update%20and%20insert%20or%20remove%20a%20row%20the%20same%20way.%3C%2FLINGO-BODY%3E
New 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 (New 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.