Feb 18 2022 04:19 PM
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 added throughout the day, every day. Some day there may be two entries, another day there may be twenty. In the other sheet, one new row is added at midnight. This is where I need help. I need a formula that will 1) reference the other sheet, and 2) pull the running total associated with that day as it changes.
For example, today (2/18/22) a new row is created in Sheet 1 with a column header of 'Date'. Throughout the day, new rows are being added in Sheet 2 and the 'Running Total' column (calculated with a structured reference formula) is dynamically updated. There is also a column header of 'Date'. In Sheet 1, there is a 'Total' column dedicated to acquiring this 'Running Total'. How do I get 'Running Total" in Sheet 2 into 'Total' of Sheet 1? Remember... I have 6 tables all functioning in the same manner.
Feb 19 2022 05:00 AM
@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.
Feb 19 2022 05:37 AM
Feb 19 2022 07:58 AM
@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)
Feb 22 2022 11:12 AM
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!
Feb 23 2022 10:34 PM
SolutionOK, 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.
Feb 24 2022 03:45 PM
Feb 25 2022 10:10 AM
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.
Feb 25 2022 01:35 PM
Mar 07 2022 11:07 AM
Mar 08 2022 12:05 AM
Feb 23 2022 10:34 PM
SolutionOK, 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.