Forum Discussion
Solved
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...
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.
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 Thank you for the quick response.
I will give it a try.Best regards
