Forum Discussion

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
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.
Reference a dynamic structured reference formula in another sheet_(endDown+LET).xlsx13 KB

bosinander

Iron Contributor

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.

Reference a dynamic structured reference formula in another sheet_(endDown+LET).xlsx13 KB

eburniston

Copper Contributor

Feb 24, 2022

I entered the below formula based on the table illustrated in my previous post. I removed the IFERROR part so it wouldn't mask any errors. After removing it, the error I received was "You've entered too few arguments for this function". (btw - I had to replace the semicolons with commas. When left with semicolons, I received an error "The first argument of LET must be a valid name".) Do you see what I have done wrong? Thank you immensely for your help!!! Up until now, I have been a basic Excel user... and it shows! 🙂

=LET(dateCell,C3, dateAnchor,'2022_USAGE'!$C$3,valueAnchor,'2022_USAGE'!$G$3,allValues,valueAnchor:endDown(valueAnchor),allDates,dateAnchor:endDown(dateAnchor),foundValues,FILTER(allValues,allDates=dateCell),output,SUM(foundValues))

bosinander
Iron Contributor
Feb 25, 2022
eburniston
1. add ,output before the last parenthesis, ie
<snip>SUM(foundValues),output)
It is instead of originally ,IFERROR(output,0))

2. make sure that you have the name endDown defined (Ctrl+3 for Name Manager) or the result will be a #NAME error
if it is not there, copy a cell with endDown in the formula from the earlier attached file and paste it in your file.
endDown is a worbook local function that can be quite easy transfered by copy and paste a cell using the function.