Reference to multiple table on single sheet

Hi, I am using Excel 2019 on Windows 10. On a single sheet I have set up several tables I want to use as base data collections for futher calculations on other sheets. On another sheet I again hav...

excel

Formulas and Functions

SergeiBaklan
Sep 24, 2023
Indigo3k

Using =baseTableName[@baseTableColTitle] you say Excel to take value from the column baseTableColTitle in the table baseTableName which is in the same row (@) where formula is.

If the formula in RefTable is in row 12, it takes vale from row 12 intersected with the column in main table.

Formula in row 17 gives an error since there is no intersection of row 17 and baseTableColTitle.

For such reference you may use

=IFERROR( INDEX(baseTableName[baseTableColTitle], ROW()-ROW(RefTable[#Headers]) ), "")

If other columns are in RefTable they will be not sorted in sync with main table. If in main table are unique ID (e.g. unique names, etc) in some column, you need to use any lookup function to sync other columns.

SergeiBaklan

Diamond Contributor

Sep 24, 2023

Indigo3k

Using =baseTableName[@baseTableColTitle] you say Excel to take value from the column baseTableColTitle in the table baseTableName which is in the same row (@) where formula is.

If the formula in RefTable is in row 12, it takes vale from row 12 intersected with the column in main table.

Formula in row 17 gives an error since there is no intersection of row 17 and baseTableColTitle.

For such reference you may use

=IFERROR( INDEX(baseTableName[baseTableColTitle], ROW()-ROW(RefTable[#Headers]) ), "")

If other columns are in RefTable they will be not sorted in sync with main table. If in main table are unique ID (e.g. unique names, etc) in some column, you need to use any lookup function to sync other columns.

Indigo3k
Copper Contributor
Sep 24, 2023
SergeiBaklan Thank you for the quick response.
I will give it a try.
Best regards

Forum Discussion

Reference to multiple table on single sheet

Resources