Forum Discussion
Reference a dynamic structured reference formula in another sheet
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.
eburniston Yet another variant with a better workbook local function named endDown that, as below, vlookup's from a18 to and inclusive a20 as endDown, just like Ctrl+Arrow Key Down from A18, finds A20.
This way, the (unknown) layout of your sheets can be more flexible.
=VLOOKUP(A18:endDown(A18);Sheet2!A25:endDown(Sheet2!B25);2)
These are all very helpful, but looking at your examples and how they work, I think I may be asking the wrong question. I need the total to show the running total for that specific day, not the overall running total. Is there a formula that will query the date based on the date in the first sheet?
This brought up another question on the Running Total column in my 2022_Usage sheet. 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? Should I have a different formula? This is the one I have =SUM(Stat3_use[[#Headers],[Used gal]]:[@[Used gal]])
Thank you!
- Why don't you simply use Power Query and filter by the particular date or month and get your running total
Or in your Excel, if you have a date column >create a helper column for Month (Text(Date, "MMM")
And then you can filter it out after you have added this code for running total assuming Column A Contains the Sales =Sum($B$2:B2) 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.
- I got it to work! Thank you tremendously, for all of your assistance and patience. Your help and time is so very much appreciated!
