Reference a cell within an Excel Table

Question

I am trying to reference a cell within an Excel Table to be used within a formula in another area of the sheet.  I'm not having any luck finding how to do this.

I have an Excel Table set up on a spreadsheet that I am utilizing to capture scanned barcode information.  The data capture is working splendidly.  We can just scan right along and it fills in across and automatically starts a new row when it gets to the end of the one above.  Perfect!  However, I need to extract certain parts of the scanned barcodes (ex: a long serial number and part of the SN identifies the model, part of it is the actual identifier).

When I try to build the formula and select the cell that resides within the table, I get: Table2[@[AssetBarCode]]   "AssetBarCode" is the column heading in the table for the cell I am trying to reference.  And, obviously "Table2" is the name of the Excel Table.

I want to get the value that's in that cell.  That "reference", for lack of a better word, doesn't even provide the row info.  There's no intersecting info at all!  UGH!

Any help would be appreciated.

TIA

Answer 1

Using your data, it seems to work fine here.  Even using the structured reference.  Note that with the formula in the same row, you may only need to refer to the column header.  And with the formula within the table, you can use an unqualified (no table name) reference.

Note the formula in the formula bar, next to fx.

As to why you are seeing a formula and not a result, be  certain the cell was formatted as General BEFORE you enter the formula.  If that is not the problem, I would have to examine the workbook.

Actually, the formula in Column F, as written, will return an ARRAY of all the values in the Asset BarCode column.  You are only seeing the one that is on the same row since it displays the first, and there is only one row in your sample table.  To "force" the formula to return ONLY the one value, and not an array, you need to add the ThisRow parameter, so the formula might look like:

=RIGHT(Table1[[#This Row],[Asset Bar Code]],7)

Answer 2

Thank you

Answer 3

I turned off that option and it had no effect.

Let me take a snapshot.  A picture is worth...

I don't have "Show Formulas" turned on, so I have no idea why I am only seeing the formula.

I'll try using the wizard, but I don't see why that would change anything.

Answer 4

There are elements of the structured reference that depend on the location of your formula -- for example, if the formula is in the same row.  Otherwise, the formula wizard should just return the cell reference (e.g. B3) and the formula would return the contents of B3.  Not sure I understand your problem.

If you select the cell(s) to which you want to refer when building the formula, it should return the appropriately structured reference in the proper syntax.

Of you could just turn off the option -- Excel Options / Formulas / "Use table names in formulas"