Help referencing the above cell for filtered results

dan432

Could someone please help with the pictured formula. I'm trying to cumulative add the cell from above, However, when I filter results such as day of the week It obviously shows wrong data referencing a cell which isn't there. How do I only reference the visible cell above to show the right figure when filtered?

Many Thanks, Dan

Lucas Rayala

Hi @dan432 -- the filters are just for visibility. They can't be incorporated into a formula. I love this idea, however, and suggest you enter a Product Idea entry for vote.

I sometimes use the Sheet Summary tab to add dropdown values if I want to be able to give someone controls to change how data is displayed on the sheet. This may not be worth the effort, but I've provided a brief description below.

The Sheet Summary tab can be found on the right sidebar. It essentially allows you to create a series of standalone cells as independent entries. Each entry is basically a column with a single cell. You give each entry a name (analogous to a column name in the general sheet), and you can reference the entry's value in a cell formula. You use a hashtag instead of a row number or @row reference to reference these values. For instance, if you have a Summary entry named "Your New Entry", you could reference this entry's value within the general sheet using the notation:

[Your New Entry]#

To get to your specific need: In a very basic example, let's say you had a column with client names in your sheet and you wanted to "filter" a formula to a single client. You could create an entry in your Summary called "Client", with all your client names in a dropdown. Then, update your Sum formula so that it only added values if the client name was equal to the client name in the Client pulldown.

The devils in the details, of course, and this can get complicated, forcing you to create a ton of additional logic to get your values and page to display appropriately. This may or may not be worth the effort. Good luck!

dan432

Hi Lucas, thank you for your detailed reply. I'll be honest I pretty much know the basics on excel but from a logical stand point I though it seemed like a simple function to be able to reference the above cell to account for filters. I may have to go back to the drawing board and re think on how to present the data.