#VALUE Error when mapping fields between Excel tables

I am trying to consolidate data from tables in multiple worksheets (monthly expenses on each tab and then combine into a single worksheet). I have named each table (ex. JanExptbl) and have tried to use the simple "=" formula but cannot get it to work:

=JanExptbl[Transaction Type] where "Transaction Type" is the column header name. However, no matter what I try I am consistently receiving the "#Value" error which doesn't seem to tell me much at all.

Something tells me this is something very simple that I am missing since I haven't tried this before but I just can't figure it out.

I have attached a very basic example of what I am trying to accomplish.

Any help would be immensely appreciated!!!

Thanks
BrianTable Mapping Test.xlsx

ELeGault, thanks for the response. But too bad that I am not following what you are suggesting.

So I have figured out that by using my above formula (=JanExptbl[Transaction Type]), Excel is trying to perform a math function on the data but is unable to since it is text. When I add SUM or AVERAGE, it works like a champ without generating the error. But I don't want to perform any math function, I just want to map it to the table. I am trying to consolidate multiple tables into a single table is all.

Is this not possible?
-B

The naming of the tables is causing the problem. As previously stated, instead of clicking on the cell, manually type in =C3. Then it will work. See attached.

What i am trying to do is a little more complicated so i can't simply use =C13 in the example I initially provided. I have updated the spreadsheet with more information and a description of what I am attempting to accomplish. However, i am not sure it is even possible.

In the attached workbook, Sheet 1 has 5 tables on it. However, it the real workbook, each of the 5 tables would be on separate worksheets. I put them all on one sheet for simplicity of the example.

Each table will contain a "N" number of rows but will all have the exact same data/columns. I am trying to map all of the rows that contain information from each table into a single table (Table 1 in the example). I was going to use VLOOKUP but I don't have a column to reference and find a match. So I was going to use ISBLANK and map those cells that are not blank. But since the number of rows with information in each table can vary, I can't "hard code" the mapping as in the example of using =C13 since I won't know the last row in the table before proceeding onto the next table.

I was trying to use this formula as a simple start just to see if I can map the data from one table column into another: =Table2[[#Data],[Transaction Type]] When I type in the formula into Table 1, the column in Table 2 is highlighted as to indicate a matching reference. However, all it returns in Table 1 is the #VALUE! error.

I am unable to figure out why I am getting this error.

Brian

Table Mapping Test.xlsx

Thanks, I'll see what I can come up with for a unique identifier