Is there an easy way to build a formula that will only take values into
consideration when the auto format is used. So, if I have a table of data and
a formula based on this table and I filter one of my data columns to some
vause then I would like my formula to automatically update.

Is this possible?

Thank you,
CH

The SUBTOTAL function ignores rows that are hidden by an autofilter (and
optionally, also rows that were hidden manually). It can perform a wide range
of operations, including sums, averages, counts, standard deviations,
products, maximums, minimums, etc. Check Excel's Help on SUBTOTAL for more
information. Using SUBTOTAL in your formula is the easiest way to exclude
rows hidden by an autofilter.

Hope this helps,

Hutch

"cherman" wrote:

Is there an easy way to build a formula that will only take values into
consideration when the auto format is used. So, if I have a table of data and
a formula based on this table and I filter one of my data columns to some
vause then I would like my formula to automatically update.

Is this possible?

Thank you,
CH

From what I can tell, this will not work for me, or it is to complex.

I want to give my users 7 dropdown boxes, each populated with unique values
from a column on my raw data tab. Then, when a user chooses a value or any
combo of values from these dropdowns, I want the data on my raw data tab to
auto filter and I want my calculated values on my calculated tab to to auto
recalculate.

I guess I could use the auto filter, but I only want my users to filter on
the last 7 columns of my raw data and I don't want them to have to go to the
raw data tab to do it. I have a 3rd tab that has a chart based on the
calculated values on my calculated tab, and this is where I want the
filtering option to be. Then a user can choose to filter and see the chart
change automatically.

Maybe this has to be done in code?

I know this sounds similar to a pivot chart, but I could not get this option
to work. The chart is a combo of a bar and line graphs, 1 bar and 4 lines,
and then the 7 filtering options. I don't remember what the issues were, but
I could not get the data to display right. I guess if there is a way to use a
pivot chart to do this then this would be ideal.

Thanks!


"Tom Hutchins" wrote:

The SUBTOTAL function ignores rows that are hidden by an autofilter (and
optionally, also rows that were hidden manually). It can perform a wide range
of operations, including sums, averages, counts, standard deviations,
products, maximums, minimums, etc. Check Excel's Help on SUBTOTAL for more
information. Using SUBTOTAL in your formula is the easiest way to exclude
rows hidden by an autofilter.

Hope this helps,

Hutch

"cherman" wrote:

Is there an easy way to build a formula that will only take values into
consideration when the auto format is used. So, if I have a table of data and
a formula based on this table and I filter one of my data columns to some
vause then I would like my formula to automatically update.

Is this possible?

Thank you,
CH

On Dec 17, 8:35*pm, cherman wrote:
From what I can tell, this will not work for me, or it is to complex.

I want to give my users 7 dropdown boxes, each populated with unique values
from a column on my raw data tab. Then, when a user chooses a value or any
combo of values from these dropdowns, I want the data on my raw data tab to
auto filter and I want my calculated values on my calculated tab to to auto
recalculate.

I guess I could use the auto filter, but I only want my users to filter on
the last 7 columns of my raw data and I don't want them to have to go to the
raw data tab to do it. I have a 3rd tab that has a chart based on the
calculated values on my calculated tab, and this is where I want the
filtering option to be. Then a user can choose to filter and see the chart
change automatically.

Maybe this has to be done in code?

I know this sounds similar to a pivot chart, but I could not get this option
to work. The chart is a combo of a bar and line graphs, 1 bar and 4 lines,
and then the 7 filtering options. I don't remember what the issues were, but
I could not get the data to display right. I guess if there is a way to use a
pivot chart to do this then this would be ideal.

Thanks!



"Tom Hutchins" wrote:
The SUBTOTAL function ignores rows that are hidden by an autofilter (and
optionally, also rows that were hidden manually). It can perform a wide range
of operations, including sums, averages, counts, standard deviations,
products, maximums, minimums, etc. Check Excel's Help on SUBTOTAL for more
information. Using SUBTOTAL in your formula is the easiest way to exclude
rows hidden by an autofilter.

Hope this helps,

Hutch

"cherman" wrote:

Is there an easy way to build a formula that will only take values into
consideration when the auto format is used. So, if I have a table of data and
a formula based on this table and I filter one of my data columns to some
vause then I would like my formula to automatically update.

Is this possible?

Thank you,
CH- Hide quoted text -

- Show quoted text -

not sure if this will help? found it while looking for something else,
www.contextures.com/excelfiles.html
go to filters then - FL0015

Cheers