Forum Discussion

eburniston's avatar
eburniston
Copper Contributor
Feb 19, 2022
Solved

Reference a dynamic structured reference formula in another sheet

My document has two sheets, each containing 6 tables which are updated through Microsoft Flow. One column has a structured reference formula that keeps a running total as new rows are automatically a...
excel
  • bosinander's avatar
    bosinander
    Feb 24, 2022

    eburniston 

    OK, then the formula needs to filter the matching dates and sum their amounts.

    Since it seems you have one date per section you will probably do best using the versions that are one formula per answer in b29:b32

     

    =LET(dateCell;    A29;
     dateAnchor;  Sheet2!$F$3;
     valueAnchor; Sheet2!$G$3;
allValues;        valueAnchor:endDown(valueAnchor);
allDates;         dateAnchor:endDown(dateAnchor);
foundValues;      FILTER(allValues;allDates=dateCell);
output;           SUM(foundValues);
IFERROR(output;0)
)

     b1 and b18 do about the same but spills the result to dates below.

     

    -/-

    > If a person changes the Date column filter to just show Feb 22, why does it not change the running totals to reflect just that date?

    The quite new function filter excludes data from calculation.

    The command filter affects what is displayed on the screen - the hidden data is still there and included in calculations.

    If you'ld like to show only the displayed subtotal you could use the function subtotal(9;range) instead of sum(range) but that is apart from the above solution.

bosinander's avatar
bosinander
Iron Contributor
Feb 19, 2022

eburniston, Hi

It sounds like LOOKUP is the function you are looking for.

=LOOKUP(A3#;Sheet2!A:A;Sheet2!B:B)

ought to do it.

 

A3# fetches the actual unique dates and you should change to A3:A6 or use a worksheet local formula included in the file.

=LOOKUP(CELLS.DOWN(A3);Sheet2!A:A;Sheet2!B:B)

CELLS.DOWN(anchor_cell) is not Excel standard. It is made using the new LAMBDA function and returns all cells to the last one in that column.

Copy and paste a cell with that formula and it will follow if you paste in other workbooks.

CELLS.DOWN will be no good if you have your tables on top of each other.

Resources