I know there are many ways to create subtotabls that change based changes the visibility of cells - although my question might sound similar - this is NOT my question.
suppose I have a workbook with 2 columns and 5 rows e.g
. A B 1 Name Val 2 A 1 3 A 3 4 B 5 5 B 2 6 A 6 7 A 8 B 5 9 A 2
I am trying to fill cell B7 with an average of the some of the "A" named rows above it... So I filter the table based on the Name col using "A" what I want to get is the formula sum(B2,B3,B6) or (even better) sum(B2:B3,B6) i.e The sum of the cells that are visible during the creating of this formula.
I can not use subtotal because it would change its value when I change the filtering... in theory I could use subtotal and copy-paste as value but then I loose the semantics of the value.
Note that this value should remain constant regardles of future changes in row visibility (I don't even need it to change if values in A2,A3,A6 change from A to B - but I do expect it to change if B2, B3 or B6 are changed....)
I also know that if I choose the range B2:B6 (when still filtered to show only "A" named rows) and press ALT+; (windows) it will change the selection to be exactly B2:B3,B6 but this does not seem to work during the creation of a formula :(
Just to clarify- the real data is much bigger so picking the cells one by one is not an option.
Any ideas how to get the reference to the currently visible part of the currently selected range?