Reference to multiple table on single sheet

Copper Contributor


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 have tables referencing to the base tables. References are structured, i.e. done by "=baseTableName[@baseTableColTitle]"

That works fine...

... as long as I do not insert or delete blank rows between the tables on either sheet.

The referencing tables behave as if the references were made by absolut cell references, e.g. "=A25" and not relative to the named table. Are the structured references get converted to absolut ones internally? Please see the screenshots below for illustration.

I do not recall such a behaviour from simple named ranges.

How can I achieve the intuitively correct behaviour of the references being relative to the named table?

Thank you very much for your help.




2 Replies
best response confirmed by Indigo3k (Copper Contributor)


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.

@Sergei Baklan Thank you for the quick response.
I will give it a try.

Best regards