Forum Discussion

Indigo3k's avatar
Indigo3k
Copper Contributor
Sep 23, 2023
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 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.

 

 

 

excel
Formulas and Functions
  • SergeiBaklan's avatar
    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's avatar
    SergeiBaklan
    MVP
    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.

Resources