Jan 27 2021 03:05 PM
Hello,
I would like to know how I could possibly filter my table but have the table update the formulas that are in it so as not to include the filtered out section of the table.
Short example:
I have an invoice column, cumulative column, and average column. The invoice is inputted by hand and then the cumulative and average column work off of that data. There are 12 rows representing each month of the year.
Sometimes an order doesn't begin in january, therefore I would like to filter out the months before it. Lets say I filter out to June because that is when the order began and I received an invoice. I run into a problem where my cumulative and average columns still attempt to calculate the 5 rows taken out above them.
The spreadsheet is much more complicated with VLOOKUP in some of the beginning rows but I beli
Jan 27 2021 10:28 PM
SolutionJan 28 2021 07:07 AM
@Wyn Hopkins thank you this did help. I was able to apply it to the cumulative tabs and now I'm researching how to use it with lots of IF statements.
Jan 28 2021 07:41 AM
@Wyn Hopkins My searches have been futile so far. Would you happen to know how to use aggregate with IF statements. My original formula was this: =IF(ISBLANK(C32),E31,E31+(C32/B32)). But it doesn't work when I hide rows.
I changed the formula to this: =AGGREGATE(4,7,IF(ISBLANK(C32),E31,E31+(C32/B32))). It still works until I add a value to C32. This it gives me the #Value error.
Jan 29 2021 04:41 PM
Jan 30 2021 02:15 AM
With first 13 aggregations, in particular with MAX (#4) you shall use references, not formulas. Most reliable way is to add helper column with formula like =(AGGREGATE(3,5,A1) which returns 0 for hided row and 1 otherwise. Here as column A we take any column in each row of which we have any data. Assuming helper column is F, you may use =IF(F1, my formula, 0).
Jan 27 2021 10:28 PM
Solution