Formula that will be affected by data filtering

**assaf.tom** · 02-19-2008, 05:11 AM

Hi,
I'm working with Excel 2007. I've imported data from a DB table to a data sheet, and added a chart, and a simple formula, based on the imported data.
When I filter the data, the chart is updated and present the filtered data, but the formula does not change its data range when filtering and allways displays the same static result.
Is there a way to force that formula to update its data input when filtering?
Thanks.

**broro183** · 02-19-2008, 06:31 AM

hi,

I'm only using Excel 2003 but it may be similar in 2007 - if the simple formula is a sum, I suggest trying a subtotal instead.

The Help Files for Excel 2003 state:

For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal only nonhidden numbers in a list.
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

If your formula is not a sum, can you please let us know what it is?

hth
Rob